Fetching count from database by days

Question

I have the following structure:

id | some_foreign_id | date
1    5                 2015-09-29 23:14:23
2    5                 2015-09-29 14:13:21
3    8                 2015-09-28 22:23:12

For the specified some_foreign_id I want to return the count of rows in this table for each day from last 2 weeks. I created this:

SELECT DATE(t.sent_at), COUNT(*)
FROM table t
INNER JOIN sometable st ON st.some_id = t.id
INNER JOIN someOtherTable sot ON sot.someother_id = st.id
WHERE t.sent_at >= DATE_ADD(CURDATE(), INTERVAL -14 DAY)
AND t.some_foreign_id = 5
GROUP BY DATE(t.sent_at);

It shows some results, but:

Doesn't show 0's if the day has 0 records.
Changing interval to -15 changes the count from the last day - don't know why.

How could I do this properly?

You've got it, for id `5` it should return 2 rows for `2015-09-29`, and 0 rows for all other days from previous 2 weeks. — khernik, Oct 06 '15 at 08:59

score 0 · Answer 1 · edited May 23 '17 at 11:52

To solve 1., you'll need to left join to something like this and use IFNULL()

To solve 2. (or try to), try changing your query to this (I suggest you solve this first):

SELECT DATE(t.sent_at), COUNT(*)
FROM table t
INNER JOIN sometable st ON st.some_id = t.id
INNER JOIN someOtherTable sot ON sot.someother_id = st.id
WHERE DATE(t.sent_at) >= DATE(DATE_ADD(CURDATE(), INTERVAL -14 DAY))
AND t.some_foreign_id = 5
GROUP BY DATE(t.sent_at);

Fetching count from database by days

1 Answers1