用例表达式获取每个条件的计数和百分比

Question

我想要一个类似于下表的输出，其中我可以使用Case表达式从查询中获得计数和百分比。表中的数字仅供说明。

10% td> 20% td>10% td>40% td>20% td>

您需要根据该CASE表达式进行分组。您可以将其放置在CROSS APPLY中，以避免重复。

VALUES返回一个虚拟表，如果您使用CROSS APPLY，您可以将它连接到每一行，同时能够从先前引用的表中插入值

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;

---

使用join也更容易做到这一点

在本例中，我们构造了一个包含可能选项的虚拟表，并以常规方式将其连接到每一行

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');