计算组中的每个条件 [英] Count each condition within group

问题描述

对于每个独特的 GroupId 我想获得每个 IsGreen ， IsRound ， IsLoud 条件和总行数。

data：

  ------------- ---------------------------------------- 
 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。[C​​ount] AS TotalLoud 
 FROM（
 SELECT GroupId 
，count（*）AS [Count] 
 FROM TestData 
 GROUP BY GroupId 
）TotalRows 
 INNER JOIN b 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] 的行数 sum 来计数每个属性。

  $ b，count（[GroupId]）as [TotalRows] 
，sum（[IsGreen]）as [TotalGreen] 
，sum（[IsRound]）as [TotalRound] 
，sum [IsLoud]）as [TotalLoud] 
 from [TestData] 
 group by [GroupId] 
  

For every unique GroupId I would like to get a count of each IsGreen, IsRound, IsLoud condition and a total number of rows.

Sample data:

-----------------------------------------------------
 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

Desired result:

 ----------------------------------------------------------
 GroupId | TotalRows | TotalGreen | TotalRound | TotalLoud
 --------+-----------+------------+------------+-----------
    1    |     2     |     1      |     2      |     1
    2    |     3     |     1      |     0      |     1
    3    |     2     |     0      |     0      |     0

I'm using the following code to create the table, the problem I'm having is that if any of the groups have no rows that match one of the conditions that group does not appear in the final table. What is the best way to accomplish what I want to do?

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

解决方案

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]

这篇关于计算组中的每个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！