TSQL:根据分组的标准，对不同的值进行总体计数

Question

我有

xy

Groupvar	Subvar	Val
G1	A
G1	A	x
G1	B	x
G1	B
G1	C	z
G1	C	z
G2	A	x
G2	A	x
G2	B	y
G2	B	z
G2	B	w
G2	C	z

您需要COUNT()聚合函数中的CASE表达式来应用条件聚合：

SELECT Groupvar,
COUNT(DISTINCT Val) [All], 
COUNT(DISTINCT CASE WHEN Subvar = 'A' THEN Val END) A, 
COUNT(DISTINCT CASE WHEN Subvar = 'B' THEN Val END) B, 
COUNT(DISTINCT CASE WHEN Subvar = 'C' THEN Val END) C
FROM tablename
GROUP BY Groupvar;

请参阅演示