[RESOLVED] Selecting data grouped by day including days without data??

rebelo

I have a table, let's call it "log" and in this table there is a date column in datetime format (yyy-mm-dd hh:mm:ss) and a text column called log (for example's sake). I need to do a report which shows all of the log entries between START_DATE and END_DATE. Easy enough. The problem is that I want the results to be grouped by DAY in this report, such that it would look something like:

DAY | TOTAL
2007-01-01 | 10
2007-01-02 | 8
2007-01-03 | 0
2007-01-04 | 5

The tricky part here is that if there were no entries made on 2007-01-03, then there would be no row with that date in it. As such, instead of the above example, I get back 3 rows (with no row where the 2007-01-03 should be).

Is there any way at all I can make mysql return a row for each date between START and END with empty data to go with the dates for which there is no data?

I know this could be worked out programmatically outside of the query, but because of reasons not worth getting into (automatic sorting and report generation), I need to get this all into the query result. Doesn't mysql have some magic way of generating it's own list of dates somehow to step through and fill in the blanks?

Thanks

Concat

Maybe this will help?

SELECT DAY(date_column) "day", COUNT(DISTINCT id_column) "count" FROM this_table GROUP BY DAY(date_column);

Probably:

SELECT DAY(date_column) "day", COUNT(DISTINCT id_column) "count" FROM this_table WHERE  date_column>=(CURRENT_DATE()-7) GROUP BY DAY(date_column)

date_column -> DATE
id_column -> ID
this_table -> TABLE

It works in my database.

Maybe * would be better to get more fields 😛

SELECT *, COUNT(DISTINCT id_column) "count" FROM this_table WHERE  date_column>=(CURRENT_DATE()-7) GROUP BY DAY(date_column)

Sorry. Tell if it works or not

rebelo

I think you misunderstand me, or I misrepresent my issue.

However, I am going to mark this resolved now because I have pretty much determined that what I wanted to accomplish is not possible. The closest option I was able to find was creating a temp table populated with the dates and then joining against that with the data table to get those empty rows with the dates without data. Instead, I just went with a code-based solution so that the data is evaluated while the table is built (using a list of dates generated by php) and then empty rows are inserted for the missing dates. It's very non-ideal, but it gets the job done.

Thanks anyways.