Welcome to WebmasterWorld Guest from 184.72.148.222

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

How To Return MySQL results with left as all dates?

     
7:04 am on Mar 15, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 17, 2003
posts:687
votes: 0


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.

7:33 am on Mar 15, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15755
votes: 0


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

11:11 pm on Mar 30, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 17, 2003
posts:687
votes: 0


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.

11:38 pm on Mar 30, 2005 (gmt 0)

Senior Member from MT 

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 1, 2003
posts:1843
votes: 0


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

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members