Sorry, I grouped over the DateAdded Field too. You have to remove it from the Select and Group BY statement.
Gert Richard Cooper schrieb: > Hi, > > I have a table (ordersonline) that may have multiple entries for each client > within the client table (clients). > > I wanted to be able to see the total spend for for each client sum(ORD.price) > as totalprice but this just returns the first price for that client. > > Here's the SQL > > SELECT ORD.orderGroupID > ,count(ORD.orderID) as numbProducts > ,ORD.dateAdded > ,ORD.price > ,CLI.fname > ,CLI.sname > ,CLI.city > ,CLI.clientID > ,sum(ORD.price) as totalprice > ,sum(ORD.discount) as totaldiscount > FROM ordersonline as ORD > LEFT JOIN > clients as CLI > ON ORD.clientID = CLI.clientID > WHERE ORD.active = '1' > AND ORD.status = 'Authorised' > GROUP BY ORD.orderGroupID > ,ORD.dateAdded > ,ORD.price > ,CLI.fname > ,CLI.sname > ,CLI.city > ,CLI.clientID > ORDER BY ORD.dateAdded > > Can you see what I'm doing wrong? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:261980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
