MY Sql Query
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
SUM(amount) as 'Amount1',0 as 'Amount2'
from MYtable
where accountid=4
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
union
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
0 as 'Amount1',
SUM(amount) as 'Amount2'
from MYtable
where accountid=3
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
but when i execute query i am getting data in this way..
MonthYear Amount1 Amount2
Jan 2015 0 100
Jan 2015 100 0
Feb 2015 200 0
Feb 2015 0 300
Mar 2015 400 0
Mar 2015 0 500
But i want Data in this format
MonthYear Amount1 Amount2
Jan 2015 100 100
Feb 2015 200 300
Mar 2015 400 500
Thx in advance...
What I have tried:
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
SUM(amount) as 'Amount1',0 as 'Amount2'
from MYtable
where accountid=4
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)
union
select CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120) as 'monthyear',
0 as 'Amount1',
SUM(amount) as 'Amount2'
from MYtable
where accountid=3
group by CONVERT(CHAR(4), dateofentry, 100) + CONVERT(CHAR(4), dateofentry, 120)