Home > OS >  How to fetch count from the sub-table with multiple groups by using MySQL
How to fetch count from the sub-table with multiple groups by using MySQL

Time:04-08

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

  • Related