对于每个唯一的GroupId,我想得到每个IsGreen,IsRound,IsLoud条件和总行数的计数.
样品数据：
----------------------------------------------------- id | ItemId | GroupId | IsGreen | IsRound | IsLoud ----+--------+---------+---------+---------+--------- 1 | 1001 | 1 | 0 | 1 | 1 2 | 1002 | 1 | 1 | 1 | 0 3 | 1003 | 2 | 0 | 0 | 0 4 | 1004 | 2 | 1 | 0 | 1 5 | 1005 | 2 | 0 | 0 | 0 6 | 1006 | 3 | 0 | 0 | 0 7 | 1007 | 3 | 0 | 0 | 0
所期望的结果：
----------------------------------------------------------
GroupId | TotalRows | TotalGreen | TotalRound | TotalLoud
--------+-----------+------------+------------+-----------
1 | 2 | 1 | 2 | 1
2 | 3 | 1 | 0 | 1
3 | 2 | 0 | 0 | 0
我使用以下代码来创建表,我遇到的问题是,如果任何组没有与匹配组中没有出现在最终表中的条件之一的行.什么是完成我想做的最好的方法？
SELECT total.GroupId,total.[Count] AS TotalRows,IsGreen.[Count] AS TotalGreen,IsRound.[Count] AS TotalRound,IsLoud.[Count] AS TotalLoud
FROM (
SELECT GroupId,count(*) AS [Count]
FROM TestData
GROUP BY GroupId
) TotalRows
INNER JOIN (
SELECT GroupId,count(*) AS [Count]
FROM TestData
WHERE IsGreen = 1
GROUP BY GroupId
) IsGreen ON IsGreen.GroupId = TotalRows.GroupId
INNER JOIN (
SELECT GroupId,count(*) AS [Count]
FROM TestData
WHERE IsRound = 1
GROUP BY GroupId
) IsRound ON IsRound.GroupId = TotalRows.GroupId
INNER JOIN (
SELECT GroupId,count(*) AS [Count]
FROM TestData
WHERE IsLoud = 1
GROUP BY GroupId
) IsLoud ON IsLoud.GroupId = TotalRows.GroupId
解决方法
您可以使用count来计算每个[GroupId]的行数,并对每个属性进行计数.
select [GroupId],count([GroupId]) as [TotalRows],sum([IsGreen]) as [TotalGreen],sum([IsRound]) as [TotalRound],sum([IsLoud]) as [TotalLoud] from [TestData] group by [GroupId]
