BenWeston Posted April 10, 2014 Share Posted April 10, 2014 Hi all I have a MySQL table of product reviews that, among others, has one TIMESTAMP column (date) with the date the review was left and another with a unique ID for the review (which I'm not currently using for this query as I don't think it's necessary?). I'd like to show reviews per day on a graph and am trying to get a list of dates with the number of reviews left on each one. The following query isn't working and is returning some very odd numbers – what am I missing? SELECT date, COUNT(*) AS graphcount FROM `reviews` GROUP BY DAY(date) ORDER BY date ASC For example, this query is returning '18' against the date 2014-04-08 19:21:28, when it should be 2! Link to comment https://forums.phpfreaks.com/topic/287667-behaviour-of-count/ Share on other sites More sharing options...
Barand Posted April 10, 2014 Share Posted April 10, 2014 Try SELECT DATE(date) as date, COUNT(*) AS graphcount FROM `reviews` GROUP BY DATE(date) ORDER BY date ASC Link to comment https://forums.phpfreaks.com/topic/287667-behaviour-of-count/#findComment-1475651 Share on other sites More sharing options...
BenWeston Posted April 10, 2014 Author Share Posted April 10, 2014 THANK YOU! That worked a treat. is DATE()'s default behaviour to group by day then? Link to comment https://forums.phpfreaks.com/topic/287667-behaviour-of-count/#findComment-1475653 Share on other sites More sharing options...
Barand Posted April 10, 2014 Share Posted April 10, 2014 DATE() extracts the date portion from a DATETIME field. DAY() extracts the day of the month http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html Link to comment https://forums.phpfreaks.com/topic/287667-behaviour-of-count/#findComment-1475656 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.