> I get an "Ambiguous column name 'logdate'." When I use this.
Oh, you just have to apply cl2. to the beginning of all your references to "logdate" in this query, and then it should work... you just can't have a column name without the table name or alias when more than one table in the query contain the same column name. And since both are the same table, you have to use your aliases to get data out of either table. That's what it means by "ambiguous" -- when you say "logdate" it doesn't know whether you mean cl1.logdate or cl2.logdate. hth Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > -----Original Message----- > From: Dan Haley [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 11, 2002 12:56 PM > To: CF-Talk > Subject: RE: Man this query @#%$&* > Can you just do a join between the two? > SELECT COUNT(*) as count_ttl, > DATEPART(dy, logdate) as doy, > DATEPART(yyyy, logdate) as yr, > DATEPART(mm, logdate) as mnth, > DATEPART(d, logdate) as dy, > count(cl1.logdate) as count_in > FROM customer_log as cl2, customer_log as cl1 > WHERE logdate BETWEEN #start# AND #DateAdd('D', 1, end)# > and cl1.logdate = cl2.logdate > and cl1.logtype <> 'callout' > GROUP BY DATEPART(dy, logdate), DATEPART(yyyy, logdate), > DATEPART(mm, logdate), DATEPART(d, logdate) > ORDER BY DATEPART(dy, logdate) > Dan > -----Original Message----- > From: Chris [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 11, 2002 10:42 AM > To: CF-Talk > Subject: Man this query @#%$&* > Can anyone out there assist me with this query? I need to count two > sets, differentiated by a text field and grouped by yet another field. > Here's what I have: > SELECT COUNT(*) as count_ttl, > DATEPART(dy, logdate) as doy, > DATEPART(yyyy, logdate) as yr, > DATEPART(mm, logdate) as mnth, > DATEPART(d, logdate) as dy, > (SELECT COUNT(*) > FROM customer_log as cl1 > WHERE logtype <> 'callout' > AND cl1.logdate = cl2.logdate) AS count_in > FROM customer_log as cl2 > WHERE logdate BETWEEN #start# AND #DateAdd('D', 1, end)# > GROUP BY DATEPART(dy, logdate), DATEPART(yyyy, logdate), > DATEPART(mm, logdate), DATEPART(d, logdate) > ORDER BY DATEPART(dy, logdate) > The problem is that cl2.logdate is not used in the GROUP BY clause nor > can I use it. Logdate contains a time stamp which screws up the GROUP > BY day functionality which is necessary for my report, thus the > DATEPART. Basically, I'm counting all lines in a date range but also > need to count just the lines in which the logtype is a specified value. > HELP! ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
