SQL grouped语句，带count和percentage

Question

我有一个这样的表(简化版)

ID1  ID2  Status
---  ---  ------
1    33     0 
1    33     0 
1    33     1 
1    33     1 
1    34     1 
1    34     2 
2    33     0
2    33     0
2    34     0

我想要获得一个计数，即按状态类型分组的状态总数，以及它占与特定ID2 ID1 &ID2相关的状态数的百分比

输出示例如下

ID1  ID2  Status   Count   Percentage
---  ---  ------   -----   ----------
1    33     0        2         50%
1    33     1        2         50%
1    34     1        1         50%
1    34     2        1         50%
2    33     0        2         100%
2    34     0        1         100%

到目前为止，我只能得到计数，但不能得到百分比。这是我现在的问题

select ID1, ID2 ,  status, count(ID2) as Count 
 from 
StatTable
 group by ID1, ID2, status

Answer 1

您可以通过使用窗口函数获取每个id1、id2组合的总百分比来获得百分比：

select ID1, ID2 ,  status, count(*) as cnt,
       count(*) * 100.0 / count(*) over (partition by id1, id2) as Percentage
from StatTable
group by ID1, ID2, status;

Answer 2

我对戈登查询做了一些小改动，现在它与你预期的答案相匹配-

select ID1, ID2 ,  status, count(*) as cnt,
       100.0 / count(*) over (partition by id1, id2) as Percentage
from table1TEmp
group by ID1, ID2, status;

Answer 3

这对我很有效

select ID1, ID2 ,  status, count(ID2) as cnt,
       count(ID2) * 100.0 / (select count(ID2) from StatTable group by ID1) as Percentage
from StatTable
group by ID1, ID2, status;