How to fetch count from the sub-table with multiple date-CodePudding

I have a user table

id	name	number	created_at
1	User1	102	2022-02-03
2	User2	103	2022-02-06
3	User3	123456	2022-02-07
4	User4	1234567	2022-02-07
5	User5	1234568	2022-04-08

and invite table

id	invited_by	invited_to_number	created_at
1	1	123456	2022-04-05 12:03:03
2	2	123456	2022-04-05 17:13:23
2	1	1234567	2022-04-07 17:13:23
2	1	1234568	2022-04-07 17:13:23
2	2	1234565	2022-04-08 17:13:23

I need user table records along with the count of all invited registered in all-time accepted in Today accepted in yesterday accepted in with the group by number using MySQL Query Count will only go to those users who were invited first and after successfully registered count will add to the invited the user

So, the Expected result is Today Date: 2022-04-08

id	name	all_time_accepted	Today's accepted	yesterday accepted
1	User1	3	1	1
2	User2	0	0	0
3	User3	0	0	0
4	User4	0	0	0
5	User5	0	0	0

CodePudding user response：

If you want to count only today's or yesterday's invites, place a boolean condition (like DATE(i.created_at) = @today_date) inside the select expression. The condition will evaluate to 1 (if true) or 0 (if false), which means that if you SUM(), you will get the number of rows satisfying this condition.

SELECT
  u.id,
  u.name,
  SUM(
    u1.id IS NOT NULL
    AND i1.id IS NULL
  ) AS all_time_accepted,
  IFNULL(SUM(
    u1.id IS NOT NULL
    AND i1.id IS NULL
    AND DATE(i.created_at) = @today_date
  ), 0) AS today_accepted,
  IFNULL(SUM(
    u1.id IS NOT NULL
    AND i1.id IS NULL
    AND DATE(i.created_at) = @today_date - INTERVAL 1 DAY
  ), 0) AS yesterday_accepted
FROM 
  (SELECT @today_date := CURDATE()) AS var,
  `user` u
LEFT JOIN `invite` i
  ON i.invited_by = u.id
LEFT JOIN `user` u1
  ON u1.number = i.invited_to_number
LEFT JOIN `invite` i1
  ON i1.invited_to_number = i.invited_to_number
  AND i1.created_at < i.created_at
GROUP BY u.id