How to group number of rows per day also in no-row days in MySQL?
So i have this table. It has some hundreds of rows. Every row has a datetime field in it. And what i need to acomplish is to get how much rows there are in given period of time but not for the whole period but for every day in this period. Till this point i know what to do. But moreover i need to have also rows for the days that does not have any row in the table with value 0.
So for example:
2012-01-01 12:13 2012-01-01 43:32 2012-01-03 23:32
Should give me the result like this:
2012-01-01 2 2012-01-02 0 2012-01-03 1
Anyone can help??
To deal with dates with 0 corresponding records, my normal practice is to use a calendar table to join on.
For example, create a table with one field called
calendar_date and populate it with every date from
1st Jan 2000 to
31st Dec 2070, or some other range that suits your reporting purposes.
Then use something like...
SELECT calendar.calendar_date, COUNT(*) FROM calendar LEFT JOIN yourData ON yourData.timeStamp >= calendar.calendar_date AND yourData.timeStamp < calendar.calendar_date + 1 WHERE calendar.calendar_date >= '01 Jan 2012' AND calendar.calendar_date < '04 Jan 2012' GROUP BY calendar.calendar_date
This table can have many extra uses, such as flagging bank holidays, starts of weeks and months. With prudent uses of flags and indexes, you can get a lot out of it.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]