I've tried the suggestions I've found on SO and cannot get the results I'm looking for which is this: I am able to get the first two columns with this code: -- What I've tried: Trying to use a calcuation in a subquery to represent the total number of rows in the table is beyond me so far, so I

SQL Server: Counts and Percentage by Group

I’ve tried the suggestions I’ve found on SO and cannot get the results I’m looking for which is this:

AgeGroup        PeopleInAgeGroup    Percent
1-Teens         1                   xx.x %
2-YoungAdults   1433871             xx.x %
3-Middle-Aged   1045337             xx.x %
4-OlderAdults   220731              xx.x %
5-Seniors       54751               xx.x %

I am able to get the first two columns with this code:

SELECT AgeGroup, Count(*) AS PeopleInAgeGroup
FROM FullData
GROUP BY AgeGroup
ORDER BY AgeGroup

— What I’ve tried:

Trying to use a calcuation in a subquery to represent the total number of rows in the table is beyond me so far, so I thought I’d just use the actual number. But all I get is zeros in the result.

SELECT AgeGroup, Count(*) / 2754691 AS Portion
FROM FullData
GROUP BY AgeGroup
ORDER BY AgeGroup

Above code results in this:
AgeGroup        Portion
1-Teens         0
2-YoungAdults   0
3-Middle-Aged   0
4-OlderAdults   0
5-Seniors       0

These are some other things I’ve tried based on the posts I found here on SO:

The query below returns all zeroes, too:

SELECT AgeGroup, COUNT(*) as GroupCount,
    AVG(case when AgeGroup = '5-Seniors' then 100.0 else 0 end) AS Portion
FROM FullData
GROUP BY AgeGroup
ORDER BY AgeGroup

This query follows a similar example I found on SO but it does not give the results I need:

SELECT AgeGroup, ROUND(100 * COUNT(*) / tot_count,2)
FROM (SELECT COUNT(*) as tot_count, AgeGroup cat
    FROM FullData
    GROUP BY AgeGroup) AS sub,
    FullData
WHERE AgeGroup = '5-Seniors'
GROUP BY AgeGroup, tot_count
ORDER BY AgeGroup, tot_count

/* RESULTS:
AgeGroup    (No column name)
5-Seniors   5475100
5-Seniors   100
5-Seniors   24
5-Seniors   5
5-Seniors   3
*/

Answer

You can use a window function and arithmetic:

SELECT AgeGroup, Count(*) AS PeopleInAgeGroup,
       count(*) * 1.0 / sum(count(*)) over () as ratio
FROM FullData
GROUP BY AgeGroup
ORDER BY AgeGroup;

This returns a ratio between 0 and 1. If you want a value between 0 and 100 multiply by 100.0 instead.

Advertisement

Answer