I have a table called date_count with 2 columns one is a value and another is a date time, say called date_time in mysql. Date Time in mysql is stored as dd-mm-yy hh:mm:ss. The issue comes when I need to get the number of rows per day. As its stored as date time format in mysql , you need to specify both date and time when you specify a group by. So grouping by day is not possible in that case. So we need to use the in-build mysql function DATE(). The function accepts date time as parameter and returns only date back. So we just need to use the DATE() function where we need to group by date and all the date time values are converted to date and can be grouped by date. So here is the implementation of it.
SELECT COUNT(*),<strong>DATE(date_time)</strong> AS date FROM date_count GROUP BY date ORDER BY date DESC
So what the query does is , it converts date time to date and groups by date and sorts the date making the most recent top most in the result.
The AS is a type casting by which we can mention that column using that name specified ( here it is date). So thats it. Any queries? Put as comments..
Recent Comments