Hi Mike, If you use a LEFT JOIN, I think you'll get the results you want. Something like
SELECT afs.stat_date, afs.hits, COUNT(v.clientcode) AS signups FROM affiliate_stats AS afs LEFT JOIN vtconlineusers AS v ON (v.creation_date=afs.stat_date AND v.dealercode=afs.affiliate_id) WHERE affiliate_id='a280' AND afs.stat_date BETWEEN '2003-01-01' AND '2003-09-31' GROUP BY afs.stat_date Hope that helps. Matt ----- Original Message ----- From: "Mike Morton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 02, 2003 6:27 PM Subject: Query not returning 0 count records.... > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
