SQL query to sum separate columns with separate Where conditions

Question

SQL noob here. I want to sum two columns separately, using 2 separate where conditions.

ID  NAME    VALUE1   VALUE2
--- ------- -------  -------
1   Orange  5        30
2   Orange  11       30
3   Orange  7        15 
4   Pear    12       12 
5   Pear    13       25 
6   Pear    4        25

Sum of VALUE1 column where values are > 10
Sum of VALUE2 column where values are > 20
Grouped by NAME

Desired output:

NAME    VALUE1   VALUE2
------- -------  -------
Orange  11       60
Pear    25       50

Thanks!

Gordon Linoff · Accepted Answer · 2020-05-30 16:26:34Z

2

Use conditional aggregation:

select name, sum(case when value1 > 10 then value1 end),
       sum(case when value2 > 20 then value2 end)
from t
group by name;

answered May 30, 2020 at 16:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

AGaur · Accepted Answer · 2020-05-30 18:00:43Z

0

I know this approach is bit tricky and lengthy as well,however this might be helpful in debugging ,if you can try this :

select 
value1.name,value1.a,value2.b 
from 
(select sum(value1)a,name from test where value1 >10 group by name) as value1
join (select sum(value2)b,name from test where value2 >20 group by name) as value2
on value1.name = value2.name

answered May 30, 2020 at 18:00

AGaur

3351 gold badge2 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL query to sum separate columns with separate Where conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related