BenWeston Posted April 10, 2014 Share Posted April 10, 2014 (edited) 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! Edited April 10, 2014 by BenWeston Quote Link to comment 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 Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.