Sum on same column with two different conditions mysql

Question

I have a table named Order with schema as

user_id, state    amount
11       success  100
11       FAILED   10
11       FAILED   10
11       success  17

state can have two values (Success/Failed).

I want to fetch sum(amount) when state = "SUCCESS" - sum(amount) when state = "FAILED" means difference total amount when success - total amount when failed.

I can solve this problem in 2 queries.

A = select id, sum(amount) when state = "SUCCESS"
B = select id, sum(amount) when state = "FAILED"

And solution will be A-B. Is there any way I can achieve this in single sql query?

Zaynul Abadin Tuhin · Accepted Answer · 2019-04-12 11:05:06Z

5

use case when

    select user_id,sum(case when state = 'SUCCESS' then amount else 0 end)-sum(case when state = 'FAILED' then amount else 0 end)
from table group by user_id

answered Apr 12, 2019 at 11:05

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-04-12 11:24:39Z

1

Use conditional aggregation:

select id,
       sum(case when state = 'SUCCESS' then amount else - amount end) as total
from t
where state in ('SUCCESS', 'FAILED')
group by id;

I assume that you want this sum per id and not overall in the table.

edited Apr 12, 2019 at 11:24

answered Apr 12, 2019 at 11:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

jarlh Over a year ago

Missing end for the case expression?

Pavel Smirnov · Accepted Answer · 2019-04-12 11:58:45Z

1

select sum(case when state = "SUCCESS" then amount else o end) -
    sum(case when state = "FAILED" then amount else o end)
    from tbl
group by userid

edited Apr 12, 2019 at 11:58

Pavel Smirnov

4,8293 gold badges22 silver badges32 bronze badges

answered Apr 12, 2019 at 11:05

Rima

1,4551 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Sum on same column with two different conditions mysql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related