CODEWITHSUNDEEP
mysqlsql
Geek_To_Learn
Geek_To_Learn

Reputation: 1956

Sum on same column with two different conditions mysql

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?

Upvotes: 6

Views: 4195

Answers (3)

Rima
Rima

Reputation: 1455

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 5

Related Questions