如何选择按日期分组的SQL结果

Question

I want to select data from following table group by date

date        btype       amount
1-2-2017    income      1000
1-2-2017    income      200
1-2-2017    Expense     100
1-2-2017    Expense     200

2-2-2017    income      1000
2-2-2017    income      500
2-2-2017    Expense     1000

3-2-2017    income      2000
3-2-2017    Expense     500

So my result should look like this.

date        Income      Expense
1-2-2017    1200        300
2-2-2017    1500        1000
3-2-2017    2000        500

Will some body guide me to how to write SQL query to achieve this behavior!

Answer 1

This is fairly straight forward SQL that you should be able to figure out yourself with a bit of google:

select date
      ,sum(if(btype = 'income', amount, 0)) as Income
      ,sum(if(btype = 'expense', amount, 0)) as Expense
from table
group by date