How to fetch count from the sub-table with multiple groups by using MySQL-CodePudding

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