Hi Richard,
in order to get the sum of all details in the ord table you should not
group over fields in the ord table that are not equal for each client.
So if you do something like this:
SELECT COUNT(ORD.orderID) AS numbProducts, CLI.fname, CLI.sname,
CLI.city, CLI.clientID, SUM(ORD.price) AS totalprice, SUM(ORD.discount)
AS totaldiscount
FROM ordersonline ORD LEFT OUTER JOIN
clients CLI ON ORD.clientID = CLI.clientID
WHERE (ORD.active = '1') AND ORD.status = 'Authorised'
GROUP BY ORD.dateAdded, CLI.fname, CLI.sname, CLI.city, CLI.clientID
ORDER BY ORD.dateAdded
Then it should do the trick.
I know, some of the fields are missing, but you can still get the
details afterwards, or by using inner select statements.
Greetings from Zürich/Switzerland
Gert Franz
Customer Care
Railo Technologies GmbH
[EMAIL PROTECTED]
www.railo.ch
Join our Mailing List / Treten Sie unserer Mailingliste bei:
deutsch: http://de.groups.yahoo.com/group/railo/
english: http://groups.yahoo.com/group/railo_talk/
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:261979
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
