mysql grouping by days

Question

i have this scheme:

+-------+
|posts: |
+----+--+--------+--------------------+
| ID |   title   |      timestamp     |
+----+-----------+--------------------+
|  1 |    t1     |2011-04-05 17:54:55 |
+----+-----------+--------------------+ 
|  2 |    t2     |2011-04-06 09:10:11 |
+----+-----------+--------------------+ 
|  3 |    t3     |2011-04-07 02:07:22 |
+----+-----------+--------------------+

How i can get the total of posts for last 7 days grouped like this:

monday: 3
Tuesday: 9
Wednesday: 2

DKSan · Answer 1 · 2017-09-19T06:24:09.690

3

Well,

you would have to select the date, make a count(*) and group by date.

SELECT date_format(TIMESTAMP, '%d %m')
       , COUNT(*)
FROM posts
WHERE TIMESTAMP BETWEEN FROMDATE AND TODATE
GROUP BY date_format(TIMESTAMP, '%d %m')

further help and explanation: MySQL Manual for DATE_FORMAT

EDIT:

Weekday can also be achieved with this function by using %W.

edited Sep 19 '17 at 06:24

answered Apr 06 '11 at 08:33

DKSan

4,187
3
25
35

score 3 · Answer 2 · answered Apr 06 '11 at 08:37

3

MySQL specific solutions:

SELECT WEEKDAY(timestamp_field) AS wd, count(*) FROM your_table GROUP BY wd;

or

SELECT count(*) FROM your_table GROUP BY WEEKDAY(timestamp_field);

answered Apr 06 '11 at 08:37

Denis Otkidach

32,032
8
79
100

works good, but when a day have zero that day isn't listed in array. – greenbandit Apr 06 '11 at 20:58
@greenbandit Do you realy need zero-count rows? All missing weekdays can be obviously filled at applitation level. – Denis Otkidach Apr 08 '11 at 08:05

score 2 · Answer 3 · edited Aug 23 '11 at 19:54

2

SELECT WEEKDAY(timestamp), count(*)
FROM POSTS as p1
WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) < timestamp
GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp)
ORDER BY YEAR(timestamp) desc, MONTH(timestamp) desc, DAY(timestamp) desc

Check out the date and time functions in MySQL.

edited Aug 23 '11 at 19:54

Chris Frederick

5,482
3
36
44

answered Apr 06 '11 at 08:34

Capitaine

1,923
6
27
46

but if some day has 0 entries, that day is lost. – greenbandit Apr 06 '11 at 21:54
1

added weekday(timestamp) for clarification. if 0 entries, rows would not be shown from one single table. unless there are other tables consisting every day information, then we can do the left join to ensure all rows are there, but it causes relatively high overhead. instead, I suggest you may do the simple checking on this in your server side. – Capitaine Apr 07 '11 at 07:49

mysql grouping by days

3 Answers3