Date split in columns in mysql-CodePudding

I have an events executed table wherein I want to separate columns based on the dates

SELECT Event_ID,Name,count(Customer_Id) FROM UNLIMITED.Event_Execution a
join UNLIMITED.Event_Master b on a.Event_Id=b.ID
Where Event_Execution_Date_ID = '20211007'
group by Event_Id
order by Count(Customer_Id) desc;

Desired output

Event_ID  Name    20211006  20211007
1         Offer1    1,218     6,876
2         Offer2   10,212     4,123

CodePudding user response：

You want conditional aggregation:

select 
  event_id,
  name,
  count(case when event_execution_date_id = '20211006' then customer_id end) as cnt_20211006,
  count(case when event_execution_date_id = '20211007' then customer_id end) as cnt_20211007
from unlimited.event_execution a
join unlimited.event_master b on a.event_id = b.id
where event_execution_date_id in ('20211006', '20211007')
group by event_id
order by count(customer_id) desc;

Side notes:

a and b are bad alias names. use something mnemonic, e.g. e for the execution table and m for the master table.
When working with several tables, qualify all columns. Which table does customer_id belong to for instance?
Why count(customer_id)? Is customer ID a nullable column? Or does it reside in the execution table and you want to be able to outer join this table at some time and get the correct count of zero? Or what is the reason to count an expression and not just rows (count(*))?