I have a user table
id | name |
---|---|
1 | User1 |
2 | User2 |
and check-in table
id | userid | venue_id | created_at |
---|---|---|---|
1 | 1 | 1 | 2022-04-06 |
2 | 1 | 2 | 2022-04-06 |
2 | 1 | 2 | 2022-04-06 |
2 | 1 | 2 | 2022-04-07 |
2 | 1 | 3 | 2022-04-07 |
2 | 2 | 1 | 2022-04-06 |
2 | 2 | 3 | 2022-04-07 |
I need user table records along with the count of all checked in all-time checked in Today checked in yesterday checked in with the group by date and venue_id using MySQL Query
Expected result Today's date: 2022-04-07 Yesterday's date: 2022-04-06
id | name | All-time Checked | Today checked | Yesterday checked |
---|---|---|---|---|
1 | User1 | 4 | 2 | 2 |
2 | User2 | 2 | 1 | 1 |
CodePudding user response:
You can use SQL
UNION Operator
CodePudding user response:
Use conditional sum to calculate the Today's and Yesterday's counts:
select
u.id,
u.name,
count(distinct concat(c.venue_id, c.created_at)) as 'All-time Checked',
count(
if(c.created_at=current_date(), concat(c.venue_id, c.created_at), null)
) as 'Today checked',
count(distinct
if(c.created_at=date_add(current_date(), interval -1 day), concat(c.venue_id, c.created_at), NULL)
) as 'Yesterday checked'
from user u
join checkin c on c.userid=u.id
group by u.id, u.name
See db-fiddle