CODEWITHSUNDEEP
mysql
Jeff Jo
Jeff Jo

Reputation: 25

mysql - select from a table with sum() and where clause

this is my table

|id | code | amount |
----------------------
|1  |  01  | 500    | 
|2  |  02  | 2500   | 
|3  |  03  | 700    | 
|4  |  04  | 500    | 
|5  |  05  | 500    | 
|6  |  06  | 500    |

i want to show the sum results of row with the code in (01,03,05) as debit and (02,04,06) as credit, so the result would look like this

|id | code | debit  | credit |
------------------------------
|   |      | 1700   | 3500   |

how can it do that ? thanks in advance :)

Upvotes: 1

Views: 45

Answers (2)

Iłya Bursov
Iłya Bursov

Reputation: 24146

another variant, which sometimes could be better neither conditional aggregation:

select sum(debit), sum(credit) from
(
select sum(amount) as debit, 0 as credit
from table where code in ('01','03','05')
union all
select 0, sum(amount)
from table where code in ('02','04','06')
) as q

actual performance depends on table structure/size/indexes, so run explain to figure out what variant is preferable

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

One option is to use conditional aggregation:

select 
    sum(case when code in ('01','03','05') then amount end) debit,
    sum(case when code in ('02','04','06') then amount end) credit
from yourtable

Upvotes: 5

Related Questions