How to change sql table values to header of result with aggregate function

Question

I have a table named sales_table like below

Date	type	amount
2021-12-01	Cash	100
2021-12-01	Google Pay	200
2021-12-01	Google Pay	300
2021-12-02	Cash	100
2021-12-02	Google Pay	200
2021-12-02	Google Pay	200

I want need some query in SQL so that I can get data in below format.

Date	Cash	Google Pay
2021-12-01	100	500
2021-12-02	100	400

Can anyone please help with this?

If a MasterCard row is inserted later, do you expect a MasterCard column too in the result? — jarlh, Dec 03 '21 at 07:51
@shadow, how did you know this is a MySQL and not MS SQL Server question? — jarlh, Dec 03 '21 at 08:50
@jarlh don't worry, I'll find an ms sql server duplicate one soon. — Shadow, Dec 03 '21 at 09:01

LukStorms · Answer 1 · 2021-12-03T08:29:56.450

1

Maybe this

select sale.Date
, sum(case when sale.type = 'Cash' 
           then sale.amount end) as Cash
, sum(case when sale.type = 'Google Pay' 
           then sale.amount end) as "Google Pay" 
from sales_table as sale
where sale.type in ('Cash', 'Google Pay')
group by sale.Date
order by sale.Date

Or this (Sql Server)

select *
from (
  select "Date", "type", amount 
  from sales_table
) src
pivot (
  sum(amount) 
  for "type" in ('Cash', 'Google Pay')
) pvt

edited Dec 03 '21 at 08:29

answered Dec 03 '21 at 08:18

LukStorms

28,916
5
31
45

1

As if there weren't dozens of duplicates for pivot tables for both mysql and ms sql server... – Shadow Dec 03 '21 at 09:03

How to change sql table values to header of result with aggregate function

1 Answers1