Sorting all data by date with Group By

This is my table.

INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('5.10.2020','d8','gelen','gbp',5000,'case')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('5.10.2020','d9','giden','usd',-4500,'case')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('5.10.2020','d10','giden','gbp',-1200,'case')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('5.10.2020','d11','giden','tl',-300,'case')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('5.10.2020','d12','gelen','eur',890,'case')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('4.10.2020','d13','giden','gbp',-800,'bank')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('6.10.2020','d14','giden','tl',-700,'bank')
INSERT INTO dbo.cash_flow(date_info,expl,flow_type,currency_type,amount,flow)VALUES ('6.10.2020','d15','gelen','eur',300,'bank')

I want to write a query that shows the flow of money by days.I wrote a query like this, but I have to specify the day:

select date_info,    
    (select sum(amount) from dbo.cash_flow where currency_type= 'tl' and date_info= '5.10.2020') as TL,
    (select sum(amount) from dbo.cash_flow where currency_type= 'usd' and date_info= '5.10.2020') as USD,
    (select sum(amount) from dbo.cash_flow where currency_type= 'eur' and date_info= '5.10.2020') as EUR,
    (select sum(amount) from dbo.cash_flow where currency_type= 'gbp' and date_info= '5.10.2020') as GBP    
from dbo.cash_flow
where date_info= '5.10.2020'
group by date_info

This query works for the day I have specified. But how can I do this for all days. I get this result when I remove the date requirement:

enter image description here

I hope I was able to explain my problem.

Answer

Use conditional aggregation:

select date_info,
       sum(case when currency_type = 'tl' then amount end) as tl,
       sum(case when currency_type = 'usd' then amount end) as usd,
       sum(case when currency_type = 'eur' then amount end) as eur,
       sum(case when currency_type = 'gbp' then amount end) as gbp
from dbo.cash_flow 
group by date_info

Advertisement

Answer