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 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
