Put simply aggregate functions combine rows into "buckets". Once combined
you can't get the details of the rows that are combined. If you need
information about and individual row you will need to use the group by
clause.
It is sort of like saying "throw all these marbles in a bucket" and then
asking "how many black marbles are there?" You can't answer if there is only
one bucket. If you use the "group by" to make a separate bucket for each
color then you can get the result you want.
If this analogy is too simplistic I apologize.
----- Original Message -----
From: "Michael Thomas" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, January 07, 2001 10:42 PM
Subject: SQL aggregate functions within queries.....
> I keep running into this same problem within my queries. The problem seems
> to be using SQL aggregate functions with some fields but not all fields
that
> Im referencing in my queries. I've seen plenty of source code for other
> queries where they work fine with that combination but mine never seem to.
>
> My problem today happens to be within a TABLE JOIN, although it doesnt
> necessarily always happen when performing JOINS. My join worked before I
> added the aggregate functions & it works fine when I just use only the
> aggregate functions. Heres the source for the 2 ways Ive tried so far:
>
> [Query 1:]
> <cfquery name="descriptions" datasource="#dsn["sections"]#" dbtype="ODBC"
> cachedwithin="#cached#">
> SELECT
>
S.AUTHOR,S.DATE_CREATED,S.DESCRIPTION,S.EMAIL,S.HITS,S.LAST_VISITED,S.RATING
,S.SECTION_ID,S.TITLE,
> S.URL,S.USER_NAME,
> Sum(R.RATED) AS TotalVotes,Sum(R.OVERALL) AS OverTotal,Sum(R.ACCURACY) AS
> AccTotal,
> Sum(R.FOCUS) AS FocusTotal,Sum(R.HELPFUL) AS HelpTotal,Sum(R.RESOURCEFUL)
AS
> ResourceTotal
> FROM #db_tbl["sections"]# S INNER JOIN #db_tbl["rate"]# R ON
> S.SECTION_ID=R.SECTION_ID
> WHERE S.SECTION='cf' AND S.CTYPE='art'
> <cfif Form.sort IS "TRUE">ORDER BY S.#Trim(sorting)#
> #Trim(order)#<cfelse>ORDER BY S.DATE_CREATED,S.SECTION_ID DESC</cfif>
> </cfquery>
>
> [Query 2:]
> <cfquery name="descriptions" datasource="#dsn["sections"]#" dbtype="ODBC"
> cachedwithin="#cached#">
> SELECT
>
SECTIONS.AUTHOR,SECTIONS.DATE_CREATED,SECTIONS.DESCRIPTION,SECTIONS.EMAIL,SE
CTIONS.HITS,SECTIONS.LAST_VISITED,SECTIONS.RATING,SECTIONS.SECTION_ID,SECTIO
NS.TITLE,SECTIONS.URL,SECTIONS.USER_NAME,
> Sum(RATINGS.RATED) AS TotalVotes,Sum(RATINGS.OVERALL) AS
> OverTotal,Sum(RATINGS.ACCURACY) AS AccTotal,
> Sum(RATINGS.FOCUS) AS FocusTotal,Sum(RATINGS.HELPFUL) AS
> HelpTotal,Sum(RATINGS.RESOURCEFUL) AS ResourceTotal
> FROM #db_tbl["sections"]#,#db_tbl["rate"]#
> WHERE SECTIONS.SECTION='cf' AND SECTIONS.CTYPE='art' AND
> SECTIONS.SECTION_ID=RATINGS.SECTION_ID
> <cfif Form.sort IS "TRUE">ORDER BY SECTIONS.#Trim(sorting)#
> #Trim(order)#<cfelse>ORDER BY SECTIONS.DATE_CREATED,SECTIONS.SECTION_ID
> DESC</cfif>
> </cfquery>
>
>
> Dont mind all that code that error code is much simpler than that. Heres
the
> error code:
>
> [Error code:]
> ODBC Error Code = S1000 (General error)
> You tried to execute a query that does not include the specified
expression
> 'AUTHOR' as part of an aggregate function.
>
> I know this can be done but I cant seem to pull it off. If anyone could
help
> It would be greatly appreciated.
>
> TIA,
> Mike
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
