How to retrieve value counts from table as separate columns with column name as value name using SQL query

userjun51 0
Viewed 20

My data looks like below in table

ID Date Type
1 2/2/2022 Check
2 4/3/2022 UPI
3 4/3/2022 Cash
4 4/3/2022 Check
5 5/6/2022 UPI

I trying to get the output in the following format:

ID Date Check UPI Cash
1 2/2/2022 1 1
2 4/3/2022 2
3 4/3/2022 2 3 2

No idea how can I get it done can some one please help me with the SQL query through which I can do it

mysql sql
1 Answers

I think the query below may help you.

select Date, 
case when Type = 'Check' then cnt else null end as Check,
case when Type = 'UPI' then cnt else null end as UPI,
case when Type = 'Cash' then cnt else null end as Cash from
(select Date,Type,count(*) as cnt from table_a group by Date,Type) as temp
Sai Ren0
Related