Thanks for the point in the right direction. Looks like I had non-integer data stored there, and when i had changed it to an integer the data was truncated. Looks like I have some data editing to do hehe.
~Steve Cutter (CFRelated) wrote: > either the TotalMile or expenses columns, within your new MySQL db, have > been converted to a type of VARCHAR, which can not take this aggregate > function. Verify the data type of these columns, so that they correspond > to some numeric data type (Integer, Float, etc.). > > Cutter > _____________ > http://blog.cutterscrossing.com > > Steve Good wrote: > >> I moved my testing environment from a windows server to a linux server. >> In doing so I had to migrate my DB from Access to MySQL. Everything >> seems to be working fine except when I query a query. Here's the error >> and the queries in question. The query that seems to be the perpetrator >> is ChartSums. Any help would be appreciated. Also, my production server >> is still windows / access based. >> >> *Query Of Queries runtime error.* >> The aggregate function [SUM(expression)] cannot operate on an >> operand of type [VARCHAR]. >> >> <cfquery datasource="jhdsn" name="JobsMilesOut"> >> SELECT * >> FROM JobsMiles >> RIGHT JOIN Logins >> ON JobsMiles.SubmittedBy = Logins.ID >> <cfif #drs# IS 1> >> WHERE ((JobsMiles.Date BETWEEN #CreateODBCDate(form.StartDate)# AND >> #CreateODBCDate(form.EndDate)#)) >> <cfelse> >> WHERE ((JobsMiles.Date BETWEEN >> #CreateODBCDate(createDate(year(now()), month(now()), 1))# AND >> #CreateODBCDate(Now())#)) >> </cfif> >> ORDER BY Date >> </cfquery> >> >> <cfquery dbtype="query" name="ChartSums"> >> SELECT >> LastName, >> FirstName, >> Sum(TotalMile) AS miles_sum, >> Sum(expenses) AS expenses_sum >> FROM JobsMilesOut >> GROUP BY LastName, FirstName >> </cfquery> >> >> Thanks in advance! >> ~Steve Good >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270378 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
