SQL中两个不同组的条件聚合

Question

这是我的数据

COUNTYID POLLUTANT TYPE EMISSION
1        A         1  
1        A         2
1        B         1
1        B         2
2        A         1
2        A         2  
2        B         1 
2        B         2
3        A         1
3        A         2
3        B         1 
3        B         2

如果我这样做了

SELECT sum(EMISSION) from table where POLLUTANT = 'A' group by COUNTYID;

我会受到污染物'A‘的污染。如何编写查询来获取以下数据：

第一列是A的和，第二列是B的和，第三列是A和B的和？

谢谢你

Answer 1

你可以用案例来过滤你需要的值

  select COUNTYID, sum(case when POLLUTANT='A'then EMISSION else 0 END)  tot_a
   , sum(case when POLLUTANT='B'then EMISSION else 0 END)  tot_b
   , sum(EMISSION)  tot_a_b
  from my_table 
  group by COUNTYID

Answer 2

您可以使用条件聚合。这会将过滤条件从where子句移动到sum()：

select countyid,
       sum(case when emission = 'A' then emission else 0 end) as A,
       sum(case when emission = 'B' then emission else 0 end) as B,
       sum(emission) as total
from t
group by countyid;

Answer 3

我想给出一些想法。我们可以使用数据透视表来回答您的问题

SELECT * from  dbo.[Table_1]
PIVOT
(Sum([type_emmssion]) for [polutant] in ([A], [B]) ) as PivotTable
 group by  [CountryId]  ;