the accountant the accountant - 3 months ago 7
MySQL Question

mysql - can I select values from the same column and show them in 2 columns result?

suppose I have a table of an account entries moves, Like

ACCOUNTS table
+-------+------+---------+
| title | side | balance |
+-------+------+---------+
| cash | debit| 500.0 |
+-------+------+---------+
| cash |credit| 300.0 |
+-------+------+---------+
| cash |credit| 600.0 |
+-------+------+---------+
#..... more than 10'000 debit and credit rows


I want to group the sum of credit rows and the sum of debit rows and show every sum of them in different column.

what I tried to do is to sum the balance on groups of sides , like

select title, side, sum(balance) from accounts group by side


I get 2 rows , one for debits sum, and another for credit sum, like

+-------+------+---------+
| cash | debit| 500.0 |
+-------+------+---------+
| cash |credit| 900.0 |
+-------+------+---------+






What I want is to get the whole result in ONE result row, the sum of debits in one field and the sum of credits in another field. I want the end result to be something like this

+-------+-------+-------+
| cash | 500.0 | 900.0 |
+-------+-------+-------+


Thanks.

Answer

You can use case

select title, sum( case side when 'debit' then balance else 0 end ), 
   sum( case side when 'credit' then balance else 0 end )
from accounts 
group by title
Comments