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:261974
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
