Getting percentage of each value in a row

Question

I have a pretty simple breakdown of Customer names, and a corresponding count of that name. What I need is a third column which gives the percent of customers with that name.

Here's an example of what I need:

|NAME|       |NAME_COUNT|    |NAME_PERCENT|
 Bob          5               41.7
 John         4               33.3
 Toby         3               25.0

I have the first two fields, but can't seem to nail down the percent.

The final part of my query looks like this:

SELECT
a.Name
a.Name_Count

FROM #NameTemp a

GROUP BY
a.Name,
a.Name_Count

ORDER BY
a.Name_Count DESC

I would think this would work in the select statement to get the percentage of each row, but it doesn't. It just gives me a value of 1 for every row in the Percent field:

a.Name_Count/SUM(a.NameCount) AS "Percent"

Need to convert the `INT` values to `NUMERIC()`. – Zack Jul 16 '18 at 15:03 — Zack, Jul 16 '18 at 15:03

score 4 · Answer 1 · answered Jul 16 '18 at 15:04

Just use window functions:

select name, name_count, name_count * 1.0 / sum(name_count) over ()
from #nametemp;

Note the * 1.0. SQL Server does integer division, so you need to convert to a format that has a decimal place.

I am guessing that you have an original table that is unaggregated. If so, doing this all in one query without a temporary table is a better solution:

select name, count(*) as name_count, count(*) * 1.0 / sum(count(*)) over ()
from names
group by name;

Getting percentage of each value in a row

1 Answers1