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
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
