You need a GROUP BY clause. Any columns, in the SELECT list, on which you don't perform a function, must be added to the GROUP BY list.
SELECT a.accountID, a.accountName, a.accountDebits, SUM(a.accountDebits) AS TotalSpent FROM account_tb a WHERE a.accountID = 222222 GROUP BY a.accountID, a.accountName, a.accountDebits ORDER BY ...however you need to sort... M!ke -----Original Message----- From: Roberto Perez [mailto:[EMAIL PROTECTED] Sent: Sunday, August 20, 2006 8:02 AM To: CF-Talk Subject: SUM within general QUERY Hi all, I'm using the SUM operator to get the total expenditure in an account. As you can see below, in the same query I'm getting other data from the same table: SELECT a.accountID, a.accountName, a.accountDebits, SUM(a.accountDebits) AS TotalSpent FROM account_tb a WHERE a.accountID = 222222 .....but CF tells me: "You tried to execute a query that does not include the specified expression 'accountID' as part of an aggregate function." My only solution so far has been to execute the SUM operator in a separate query. So my question is: can we use SUM within a general query that gets other data too, or does SUM have to go in its own query? If the first, how shoulw I modify the code above for SUM and the other fields to coexist? I'm using CFMX 6 and MS Access. Thanks, Roberto Perez ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250405 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
