Total Result by Column and Date

Question

I have a table similar to this example.

id	s_id	date
X	8	2022-03-23
X	9	2022-03-24
X	9	2022-03-24
X	10	2022-03-24

I need to get which s_id has been recorded how many times per day. First I thought of grouping and looping the dates and querying the s_id for that date. However, I know that this will cause a loss of performance on too many recordings.

How can I do it using INNER JOIN, JOIN?

If there is no record for that s_id on that date, 0 should be assigned.

I'm planning to get a result like

date	s_id	total
2022-03-23	8	1
2022-03-23	9	0
2022-03-23	10	0
2022-03-24	8	0
2022-03-24	9	2
2022-03-24	10	1

Why not grouping by `s_id` and `day` in your query? Check https://stackoverflow.com/a/46097732/5103610 — Almaju, Mar 23 '22 at 22:44
SQL TABLE. But I will loop it with php. I'll put it in a chart to observe the daily visitor differences between the sites. — Riga, Mar 23 '22 at 22:45
@Almaju If there is no record for that s_id on any date, I can't get results. — Riga, Mar 23 '22 at 22:46
Yes, obviously. You can then populate the missing rows in php or use [window functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html) but that might be overkill. You can take a look at: https://stackoverflow.com/questions/64327976/partition-by-date-range-in-window-function — Almaju, Mar 23 '22 at 22:48
@Almaju I'm sure I can handle this in one query. More ideas are needed. — Riga, Mar 23 '22 at 22:51

score 0 · Answer 1 · answered Mar 24 '22 at 00:14

You can do something like this:

SELECT t1.date, t1.s_id, COUNT(t2.s_id) AS total
 FROM
  (SELECT DISTINCT A.date, B.s_id FROM mytable A CROSS JOIN mytable B) t1
  LEFT JOIN mytable t2
  ON t1.date=t2.date
  AND t1.s_id=t2.s_id
GROUP BY  t1.date, t1.s_id
ORDER BY  t1.date, t1.s_id

The idea is to get each existing s_id attach with each existing date using a CROSS JOIN between the same table. Then make that as a subquery and do a LEFT JOIN with the same table again but this time to get the total count of each matching s_id and date from the third table. If no match, it should return 0. I suppose this query will work on almost all MySQL versions.

Demo fiddle

score 0 · Answer 2 · answered Mar 28 '22 at 03:59

I need to get which s_id has been recorded how many times per day.

That is simply

SELECT s_id, `date`, COUNT(*)
    FROM tbl
    GROUP BY s_id, `date`;

No JOIN needed. Hauling all the data back to PHP is probably more costly than simply letting MySQL do all the work.

If there is no record for that s_id on that date, 0 should be assigned.

OK, now it gets messier. First, build a table with all the desired dates; let's call it dates. (MariaDB has an easy way to do that.)

SELECT s.s_id, d.date, COALESCE(x.ct, 0)
    FROM       ( SELECT `date` FROM dates          ) AS d -- all the dates
    CROSS JOIN ( SELECT DISTINCT s_id FROM tbl     ) AS s -- all the s_ids
    LEFT JOIN  ( SELECT s_id, `date`, COUNT(*) AS ct
                    FROM tbl GROUP BY s_id, `date`
               ) AS x   ON x.s_id = s.s_id
                       AND x.date = d.date

Total Result by Column and Date

2 Answers2