conditional aggregation on two different groups in SQL

Question

Here is my data

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

if I do

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

I would get pollution from Polutant 'A'. how can I write a query to get following data:

column 1 with sum of A, column 2 with sum of B, column 3 with sum of A and B?

Thank you

Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. — user330315
– user330315, Commented Mar 2, 2018 at 14:47
Use case expressions to do conditional aggregation. But what's the expected result if pollutant D suddenly shows up? — jarlh
– jarlh, Commented Mar 2, 2018 at 14:47
It is disorienting that you are trying to sum emission which has no values. Or are you trying to count rows? — Andy G
– Andy G, Commented Mar 2, 2018 at 14:49
When your assignment gets a database you might get an answer. Show desired output. — paparazzo
– paparazzo, Commented Mar 2, 2018 at 14:49

ScaisEdge · Accepted Answer · 2018-03-02 14:51:07Z

1

You can use case for filter the value you need

  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

answered Mar 2, 2018 at 14:51

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-03-02 14:52:45Z

1

You can use conditional aggregation. This moves the filtering conditions from the where clause to the sum()s:

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;

answered Mar 2, 2018 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Anusha Subashini · Accepted Answer · 2018-03-02 21:12:40Z

0

I would like to give some idea. We can use pivot table for answer your question

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

answered Mar 2, 2018 at 21:12

Anusha Subashini

3975 silver badges17 bronze badges

Comments

sia · Accepted Answer · 2018-03-02 21:25:46Z

0

you have to use case statemet:

SELECT 
 SUM( CASE WHEN POLLUTANT = 'A' THEN EMISSION ELSE 0 END) AS A_EMISSION
 SUM( CASE WHEN POLLUTANT = 'B' THEN EMISSION ELSE 0 END) AS B_EMISSION
 SUM(EMISSION) AS total_emission
FROM table 
GROUP BY COUNTYID;

answered Mar 2, 2018 at 21:25

sia

5891 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

conditional aggregation on two different groups in SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related