Use case expression to get count and percentage for each condition-CodePudding

I want an output similar to the table below where I can get the count and percentage from a query using the Case expression. The numbers in the table are just for illustration purposes.

AgeGroup	Count	Percentage
Silent	1	10%
Boomers	2	20%
Generation	1	10%
Millennial	4	40%
Generation Z	2	20%

I ran the following query:

SELECT 
    COUNT(CASE
        WHEN birth_year between 1928 and 1945 THEN 'Silent'
        WHEN birth_year between 1946 and 1964 THEN 'Boomers'
        WHEN birth_year between 1965 and 1980 THEN 'Generation X'
        WHEN birth_year between 1981 and 1996 THEN 'Millennial'
        WHEN birth_year between 1997 and 2012 THEN 'Generation Z'
        ELSE 'Other'
    END) as 'AgeGroup'
    FROM CombinedTable

The output gives me the total count which is not what I want.

Note: I am using Microsoft SQL Server 2017

CodePudding user response：

You need to group by that CASE expression. You can place it in a CROSS APPLY to avoid having to repeat it.

VALUES returns a virtual table, and if you use CROSS APPLY you can join it onto each row, while being able to insert values from previously referenced tables

SELECT
  v.AgeGroup, 
  COUNT(*) AS Count,
  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Percentage
FROM CombinedTable ct
CROSS APPLY (VALUES (
    CASE
        WHEN birth_year between 1928 and 1945 THEN 'Silent'
        WHEN birth_year between 1946 and 1964 THEN 'Boomers'
        WHEN birth_year between 1965 and 1980 THEN 'Generation X'
        WHEN birth_year between 1981 and 1996 THEN 'Millennial'
        WHEN birth_year between 1997 and 2012 THEN 'Generation Z'
        ELSE 'Other'
    END
)) v(AgeGroup)
GROUP BY
  v.AgeGroup;

This is also more easily done with a join

In this case, we construct a virtual table of possible options, and join it to each row in the normal way

SELECT
  ISNULL(v.AgeGroup, 'Other') AS AgeGroup, 
  COUNT(*) AS Count,
  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Percentage
FROM CombinedTable ct
LEFT JOIN (VALUES
    (1928,1945,'Silent'),
    (1946,1964,'Boomers'),
    (1965,1980,'Generation X'),
    (1981,1996,'Millennial'),
    (1997,2012,'Generation Z')
) v(StartYear, EndYear, AgeGroup) ON tc.birth_year BETWEEN v.StartYear AND v.EndYear
GROUP BY
  ISNULL(v.AgeGroup, 'Other');