Group by 2 field in mysql

Question

how to group by data with 2 column but the result is in 1 row (like when we join it).

this is the table 'jembatan'

id    nama   tahun    jumlah
-----------------------------
1     A      2011     12
2     B      2011     10
3     A      2011     23
4     B      2012     11

i want the result like this:

id    totalA     totalB     tahun
---------------------------------
      25         10         2011
      0          11         2012

how to do like that?

Answer 1

You want conditional aggregation:

select sum(case when nama = 'A' then jumlah else 0 end) as TotalA,
       sum(case when nama = 'B' then jumlah else 0 end) as TotalB,
       tahun
from t
group by tahun;