How to group number of rows per day also in no-row days in MySQL?


Problem :

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??



Solution :

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.


Mysql Tutorials

Mysql Howto..

How do I make an automated task more advanced than a simple CRON job?

How to “push ahead” records with a given value?

How to count the number of times a year has been repeated from DATE type in mysql?

How to connect external mysql server in docker container

How to make java class for one tabe in mysql database

Print MySQL query to html table! How? [closed]

peewee + MySQL, How to create a custom field type that wraps SQL-built ins?

how to i take added data's id? (mysql)

how to correct run mysql command using php system() function?

How do you execute a sql file using mysql-ctl

How to get the information from table to another page using PHP and MYSQL?

How to replicate mysql from src host?

NodeJS MySQL - How to know connection is release or not

How can I speed up MySQL query with multiple joins

How to organise team work on mysql databases?

mySql: How to create a table with uknown data types?

mysql++ (mysqlpp): how to get number of rows in result prior to iteration using fetch_row through UseQueryResult

How to Disable Html codes in chat script

How to create a table in mysql with a random name?

How to use aggregate functions in mysql?

EF4.1/MVC3 Database First: How to remove password from connection string

How to create Insert script using c#

A single SELECT query showing all expressions being used (to be used as a guide) [closed]

How to convert string with date and time AM/PM to 24 Hour mysql timestamp format

How to convert a date format to mysql date format?

How to fetch SQL info from UITableViewCell an use as credentials for MYSQL parsing

How to show current names collation in MYSQL (PHP)

MySQL: how to remove all single characters from a string?

Mysql: how to properly store information from array [closed]

MySQL: How to pull information from multiple tables based on information in other tables?