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!
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
After creating a test table, I have inserted some records, and then I used this query:
select date_ as Date_Time, btype, sum(amount) as amount
from test
group by date_ , btype
Another method
select date
,sum(case when btype = 'income' then amount else 0 end) as Income
,sum(case when btype = 'expense' then amount else 0 end) as Expense
from table
group by date