Count each condition within group
You can use count
to count rows per each [GroupId]
and sum
to count each property .
select [GroupId] , count([GroupId]) as [TotalRows] , sum([IsGreen]) as [TotalGreen] , sum([IsRound]) as [TotalRound] , sum([IsLoud]) as [TotalLoud]from [TestData]group by [GroupId]
Use conditional Aggregate
. Try this.
SELECT GroupId, Count(GroupId) TotalRows, Count(CASE WHEN IsGreen = 1 THEN 1 END) TotalGreen, Count(CASE WHEN IsRound = 1 THEN 1 END) TotalRound, Count(CASE WHEN IsLoud = 1 THEN 1 END) TotalLoudFROM tablenameGROUP BY GroupId