SQL SUM values where 2 conditions are met

Question

I am trying to create a query that calculates credit and debit for each month. the values are in column called SUM and there is a field for each record that says if it is credit or debit (ex_type). I have manage to get the total sum of all the fields (although it is not correct as well - cos I am just sum all the values no matter if it is credit or debit).

SELECT sum(sum) as total FROM acc where month='$month'

But I cannot figure out for to do it for Credit and Debit

So, to summarize... I want to have the following two queries combined in one.

1. Select sum(sum) as Debit from acc where ex_type='Debit' and month='$month'
2. Select sum(sum) as Credit from acc where ex_type='Credit' and month='$month'

So, any help is much appreciated.

Is this mysql or sql-server? Do you want one value back (total) or three (Debit, Credit, Total)? — SteveB, Jan 03 '14 at 14:05

score 11 · Accepted Answer · answered Jan 03 '14 at 14:04

11

Try this with CASE

Select sum(CASE WHEN ex_type='Debit' THEN `sum` ELSE 0 END) as Debit,
sum(CASE WHEN ex_type='Credit' THEN `sum` ELSE 0 END) as Credit FROM ...

answered Jan 03 '14 at 14:04

M Khalid Junaid

63,861
10
90
118

score 1 · Answer 2 · answered Jan 03 '14 at 14:04

This should work:

Select sum(d.sum) as Debit, 
  (Select sum(c.sum) from acc c where c.ex_type='Credit' and c.month='$month') as Credit
from acc d where d.ex_type='Debit' and d.month='$month'

However if you supply more details on other fields one could inner join onto the same table and that may be slightly more efficient.

score 1 · Answer 3 · edited May 23 '17 at 12:28

1

Maybe duplicate post :

SUM() based on a different condition to the SELECT

Try this :

Select 
   SUM(CASE WHEN ex_type='Debit' THEN `sum` ELSE 0 END) as Debit,
   SUM(CASE WHEN ex_type='Credit' THEN `sum` ELSE 0 END) as Credit
FROM acc 
Where etc...

edited May 23 '17 at 12:28

Community

1
1

answered Jan 03 '14 at 14:06

Tosx

606
3
8

I saw that post, but it is a bit different I think – lStoilov Jan 03 '14 at 14:25

score 1 · Answer 4 · answered Jan 03 '14 at 14:07

I'm not a MySQL expert, but you might want to try the following

SELECT 
  SUM(CASE WHEN ex_type='Debit' THEN sum ELSE 0 END CASE) as SumOfDebit,
  SUM(CASE WHEN ex_type='Credit' THEN sum ELSE 0 END CASE) as SumOfCredit,
FROM acc
WHERE
  month = '$month'

MySQL reference 5.0 deals with the CASE statement if this doesn't work as expected

score 0 · Answer 5 · answered Jan 03 '14 at 14:05

0

SELECT
    SUM(sum) as total
    SUM(IF(ex_type='Debit',sum,0)) Debit
    SUM(IF(ex_type='Credit',sum,0)) Credit
FROM acc 
WHERE month='$month'

answered Jan 03 '14 at 14:05

Muhammad Raheel

19,823
7
67
103

score 0 · Answer 6 · edited Jan 03 '14 at 14:12

0

The simplest (and coolest) way is:

SELECT 
  SUM((ex_type='Debit') * sum) Debit,
  SUM((ex_type='Credit') * sum) Credit,
FROM acc
WHERE month = '$month'

This works because in mysql, true is 1 and false is 0.

edited Jan 03 '14 at 14:12

M Khalid Junaid

63,861
10
90
118

answered Jan 03 '14 at 14:11

Bohemian

412,405
93
575
722

SQL SUM values where 2 conditions are met

6 Answers6