Yup -- that was it. Thanks for the help.
David ------------------------------------ David Grabbe Manager, Information Systems Church of the Great God [EMAIL PROTECTED] http://www.cgg.org -----Original Message----- From: David DiPietro [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 4:03 PM To: CF-Talk Subject: RE: SQL insanity... Every item in the select statement other than the count needs to be in the group by statement -----Original Message----- From: David Grabbe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 4:01 PM To: CF-Talk Subject: RE: SQL insanity... I tried this: <cfquery name="sermon_nums" datasource="maindsn" maxrows="5"> SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS tally, sermons.title, sermons.tape, sermons.name, speakers.initials, sermons.size FROM (sermons_details INNER JOIN sermons ON sermons_details.sermon_num = sermons.tape) INNER JOIN speakers ON sermons.speaker = speakers.full_name WHERE sermons_details.date_stamp >= #Last30Days# AND sermons_details.subject = 'SERMONACCESS' GROUP BY sermons_details.sermon_num ORDER BY count(sermons_details.sermon_num) DESC </cfquery> .and I got this error: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'title' as part of an aggregate function. ideas? David ------------------------------------ David Grabbe Manager, Information Systems Church of the Great God [EMAIL PROTECTED] http://www.cgg.org -----Original Message----- From: Brian Scandale [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 3:32 PM To: CF-Talk Subject: Re: SQL insanity... Why not do a join... something like this I expect... ><cfquery name="sermon_nums" datasource="maindsn" maxrows="5"> > SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS >tally sermons.title etc, speakers.initials > FROM sermons_details JOIN sermons ON sermons_details.sermon_num = sermons.sermon_num JOIN speakers ON sermons.speaker = speaker.full_name > WHERE sermons_details.date_stamp >= #Last30Days# > AND sermons_details.subject = 'SERMONACCESS' > GROUP BY sermons_details.sermon_num > ORDER BY count(sermons_details.sermon_num) DESC ></cfquery> > >This counts up the number of detail records, and orders them. Works like a At 03:07 PM 4/17/02 -0400, you wrote: >I need some help from your SQL gurus out there :) > >Here's the deal: we are keeping a running tally of the sermons which have >been downloaded the most over the past 30 days. Every time a sermon is >downloaded, I write a record out to a sermons_details table, where the >primary key is the sermon number. This is working fine: > ><cfset TestDate = CreateODBCDate(Now())> ><cfset Last30Days = '#DateAdd("d",-30,"#TestDate#")#'> ><cfquery name="sermon_nums" datasource="maindsn" maxrows="5"> > SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS >tally > FROM sermons_details > WHERE sermons_details.date_stamp >= #Last30Days# > AND sermons_details.subject = 'SERMONACCESS' > GROUP BY sermons_details.sermon_num > ORDER BY count(sermons_details.sermon_num) DESC ></cfquery> > >This counts up the number of detail records, and orders them. Works like a >champ. The difficulty comes in when I try to get the info from the header >record for each sermon: > ><cfquery name="most_requested" datasource="maindsn" maxrows="5"> > SELECT sermons.title, sermons.tape, sermons.name, speakers.initials, >sermons.size > FROM sermons, speakers > WHERE tape IN (<cfloop query="sermon_nums">'#sermon_num#'<cfif >nums.currentrow LT 5>,</cfif></cfloop>) > AND sermons.size > 0 ---> > AND speakers.full_name = sermons.speaker ></cfquery> > >This gets the right info, but now the ordering is off -- the second query >isn't ordered according to which sermon was downloaded most. > >I'm assuming there is a way to do all of this with a single query, but so >far I haven't been able to figure it out. Oh yeah, I'm (still) using >Access2000, so the functionality of the DB is a bit limited too. Ideas? > >TIA, >David > >------------------------------------ >David Grabbe >Manager, Information Systems >Church of the Great God >[EMAIL PROTECTED] >http://www.cgg.org > > > ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists