help with error message please

The error is a straight forward SQL error. When you use an aggregate
function, eg (sum) in a select statement, all fields in the select
statement must use an aggregate function or be part of the order by cluase.

SELECT fullQuery.walkDate, sum(fullQuery.distance) AS curWeekDistance
FROM fullQuery
WHERE #week(fullQuery.walkDate)# = #curWeek#
ORDER BY fullQuery.walkDate

This is not going to solve all your problems. You are using a CF
function on th left side of the WHERE cluase. If you look at the
resulting SQL you are getting WHERE 31 = 31. This will always be true
so that you will get all the records. You need to be using the SQL
equivalent to the CF week() function. What that is will depend on which
DBMS you are using, but they all have something. You will also want to
use this function in the order by clause otherwise you will not be
getting the sums you are expecting.


jkgiven wrote:
> GOAL:
> I want to be able to add values together from the database that match the
> current week number
>
> SETUP:
> Database field walkDate is a date field
> variable curWeek finds the current week number
>
> ERROR:
> Error Executing Database Query.
>
> Query Of Queries runtime error.
> Column "fullQuery.walkDate" is invalid in the "SELECT list" clause because it
> is not contained in an aggregate function, and there is no GROUP BY clause
>
> The error occurred in \\NAWINFS02\home\users\web\b947\rh.2onboard\walks.cfm:
> line 40
>
> 38 : sum(fullQuery.distance) AS curWeekDistance
> 39 : FROM fullQuery
> 40 : WHERE #week(fullQuery.walkDate)# = #curWeek#
> 41 : </cfquery>
> 42 :
>
>
> --------------------------------------------------------------------------------
>
> SQL SELECT fullQuery.walkDate, sum(fullQuery.distance) AS curWeekDistance
> FROM fullQuery WHERE 31 = 31
>
> CODE:
> <!--- MAIN QUERY --->
> <cfset curWeek = #week(NOW())#>
> <cfset curMonth = #month(NOW())#>
> <cfset curYear = #year(NOW())#>
>
> <cfquery name="fullQuery" datasource="2onboard">
> SELECT *
> FROM bockWalk
> ORDER BY walkDate
> </cfquery>
> <cfoutput query="fullQuery"><cfset lastID=#fullQuery.recordCount#></cfoutput>
>
> <cfquery name="lastEntry" dbtype="query">
> SELECT *
> FROM fullQuery
> WHERE ID=#lastID#
> </cfquery>
>
> <cfquery name="nowWeek" dbtype="query">
> SELECT fullQuery.walkDate,
> sum(fullQuery.distance) AS curWeekDistance
> FROM fullQuery
> WHERE #week(fullQuery.walkDate)# = #curWeek# <!--- Need to add current year to
> this --->
> <!--- Need to calculate time also --->
> </cfquery>
>
> <!--- MAIN QUERY --->
> <cfset curWeek = #week(NOW())#>
> <cfset curMonth = #month(NOW())#>
> <cfset curYear = #year(NOW())#>
>
> <cfquery name="fullQuery" datasource="2onboard">
> SELECT *
> FROM bockWalk
> ORDER BY walkDate
> </cfquery>
> <cfoutput query="fullQuery"><cfset lastID=#fullQuery.recordCount#></cfoutput>
>
> <cfquery name="lastEntry" dbtype="query">
> SELECT *
> FROM fullQuery
> WHERE ID=#lastID#
> </cfquery>
>
> <cfquery name="nowWeek" dbtype="query">
> SELECT fullQuery.walkDate,
> sum(fullQuery.distance) AS curWeekDistance
> FROM fullQuery
> WHERE #week(fullQuery.walkDate)# = #curWeek# <!--- Need to add current year to
> this --->
> <!--- Need to calculate time also --->
> </cfquery>
>
>
> #nowWeek.curWeekDistance#
>