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,SECTIONS.HITS,SECTIONS.LAST_VISITED,SECTIONS.RATING,SECTIONS.SECTION_ID,SECTIONS.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
