How To Return MySQL results with left as all dates?

[AthlonInside]

Say I track a bunch of sales everyday by individual transaction, i.e. Each transaction take a row in my sales table.

I use count(*) and group by date to generate a results such as

1 Jan - 3
2 Jan - 2
3 Jan - 8
...

But the problem is, if there is 0 sales in a day, the date won't appear and my result will 'skip' that day

1 Jan - 3
2 Jan - 2
3 Jan - 8
5 Jan - 2

** 4 Jan missing if no individual sale tracked on that day.

How to I make MySQL return this instead?

1 Jan - 3
2 Jan - 2
3 Jan - 8
4 Jan - 0
5 Jan - 2

Any idea? Thank You.

[jatar_k]

how are you outputting your dates/counts?

You should be able to compare the dates that are there and if a date is missing output that date and 0

[AthlonInside]

Sorry, webmasterworld is always returning 403 to me for 2 weeks and I can't get in!

Do you mean using PHP to manually check for it? That will be quite troublesome because you will need to code some calender functions so you know which month has 30 days and which 31 and which february has 28 days and which have 29!

I am looking for a more easy solution, for exmaple with MySQL's date function, etc.

Most reports that show daily breakdown statistic will need to achieve this so I believe MySQL should be able to provide more.

[killroy]

Unfortunately u cannot return data for a row that doesn't exist. You can do two things, PHP-wise u can post process. It has date functions that will easily take care of finding hte number of days for the relevant month, or you can use a temp MySQL table into which you fill the dates.

I do seem to remember how I once did a left outer join on a dummy numbers table and managed to fill the gaps, but I can't recall how it went.

hmm I just looked it up and found a table by the name of "pivot" with these specs:

+-------+---------+------+-----+---------+----------------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+-------+---------+------+-----+---------+----------------+
¦ i ¦ int(11) ¦ ¦ PRI ¦ NULL ¦ auto_increment ¦
+-------+---------+------+-----+---------+----------------+

and filled with numbers from 1 to 10000. It's been a while since I used it though, but I'm sure if u google on mysql and pivot tables you'll find a solution.

SN