In the following query: select afs.stat_date,afs.hits,count(v.clientcode) as signups from affiliate_stats as afs,vtconlineusers as v where affiliate_id='a280' and afs.stat_date between '2003-01-01' and '2003-09-31' and v.creation_date = afs.stat_date and v.dealercode=afs.affiliate_id group by afs.stat_date order by afs.stat_date
I get a result set that returns only hits where there is a value for the count(v.clientcode) - if there is not a value for the count(v.clientcode) for that particular date, then there is not a 0 returned... Okay - a bit confusing... Here is a better attempt to explain: I am trying to do a query to list hits and signups for a service. The hits are recorded in a table called affiliate_stats and has a structure of: Affiliate id (varchar) Stat_date (date) Hits (int) The signups are recorded in a table called vtconlineusers and has a structure of: Clientcode (primary key auto_increment) Creation_date (date) And various other non-important fields. I want to get a count by date for a range of dates of signups and clickthroughs. The problem is that if there are no signups for a particular date, the number of clickthrougs is not returned in the record set either, so I end up with something like: 2003-01-01:10:1 2003-01-06:32:2 And so on when it *should* (or at least I want it to) return: 2003-01-01:10:1 2003-01-02:6:0 2003-01-03:15:0 2003-01-04:2:0 2003-01-05:8:0 2003-01-06:32:2 And so on.... So, hopefully that was a bit clearer.... Is it possible to return results like this, and if so, what is wrong with my query? TIA! -- Cheers Mike Morton **************************************************** * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * **************************************************** "Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple." - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
