How to "move" data from rows to columns with aggregate data?

Question

I count the number of occurrences by the event type, so this is the SQL query:

select date(created), 
 event_type, 
 count(*) 
from public.event 
group by date(created), event_name

enter image description here

Is it possible to change the query in some way to "move" rows to the column to form this result table, each column corresponds an event type:

enter image description here

• mustaccio: The search term you want is "pivot".
• Alocyte: Refer Pivot in PostgreSQL - explains how.

Answer 1

There are two ways to move the rows to columns either use Pivot or the case, for your example to move it to column using case,

select
date(created),
sum(case when event_type = 'a' then 1 else 0 end) a,
sum(case when event_type = 'b' then 1 else 0 end) b,
sum(case when event_type = 'c' then 1 else 0 end) c
from public.event 
group by 
date(created)

what you're going to do is creating a column with case and give a number 1 if it maches the event type and 0 if doesn't and you do that for the event depnds on the number of event you have if more than these check out pivot, then you are going to find the sum for each of these columns and group the result by the created date.