MySql Select and count number of data per day

Question

I have a following table with columns:

id    |    data    |    date

How do I query all the data in a date range from 1 to 30 days and count all data per day?

I tried something but it groups by seconds also

Select count(data) as data 
From table
Where date between '2013-12-02 00-00-00' and '2014-01-02 23:59:59'
group by date

I want to achieve

day 1  |  day 2  |  day  | day 4  | . . .
 3     |   4     |   2   |    5   | . . .

possible duplicate: http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year — Andreas Grapentin, Jan 02 '14 at 07:47

score 3 · Answer 1 · edited Dec 08 '14 at 11:19

3

Try the following

SELECT DATE_FORMAT(date, '%Y-%m-%d') AS the_date
     , COUNT(*) AS count 
  FROM table 
 WHERE date BETWEEN DATE_FORMAT('2014-11-28', '%Y-%m-%d') AND DATE_FORMAT('2014-12-05', '%Y-%m-%d') 
 GROUP 
    BY the_date

edited Dec 08 '14 at 11:19

Strawberry

33,750
13
40
57

answered Dec 08 '14 at 10:51

Farid Efendi

31
2

Thanks for the great answer, but is there a way to create row for empty dates as well, count would be 0? – Silver Ringvee Sep 11 '16 at 12:22

score 0 · Answer 2 · answered Jan 02 '14 at 07:51

0

SELECT @count:=0;
SELECT CONCAT('day ',@count+1) as day, COUNT(*), date 
FROM date between
  '2013-12-02 00-00-00'
AND
  '2014-01-02 23:59:59')
GROUP BY date

answered Jan 02 '14 at 07:51

mameyugo

298
1
6

score 0 · Answer 3 · answered May 16 '17 at 08:24

Seems like you want a query like one below:

SELECT 
CASE date_format(date ,"%W")
WHEN 'Monday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Monday' ) ,0) ELSE 0
END AS 'Monday',
CASE date_format(date ,"%W")
WHEN 'Tuesday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Tuesday' ) ,0) ELSE 0
END AS 'Tuesday',
CASE date_format(date ,"%W")
WHEN 'Wednesday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Wednesday' ) ,0) ELSE 0
END AS 'Wednesday',
CASE date_format(date ,"%W")
WHEN 'Thursday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Thursday' ) ,0) ELSE 0
END AS 'Thursday',
CASE date_format(date ,"%W")
WHEN 'Friday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Friday' ) ,0) ELSE 0
END AS 'Friday',
CASE date_format(date ,"%W")
WHEN 'Saturday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Saturday' ) ,0) ELSE 0
END AS 'Saturday',
CASE date_format(date ,"%W")
WHEN 'Sunday' THEN IFNULL((SELECT COUNT(id) FROM YOUR_TABLE WHERE 
date_format(date ,"%W") ='Sunday' ) ,0) ELSE 0
END AS 'Sunday'
FROM YOUR_TABLE

MySql Select and count number of data per day

3 Answers3