SQL question

[Guest]

Hi

We have some software which records all the shipments of goods to stores day
by day. I have a query which summrises these shipments by store on a monthly
basis eg 30 to store 1 , 25 to store 2 etc which works fine. However I now
need to show in addition to this the summary retail cost of these shipments.
I have the cost information within the tables so in theory the calc is
sum(shipments*retailcost) but when i do this it doesnt sum the info along the
lines of 30 to store 1 £45 but shows me every single shipment over the month.
Is it possible to do more than one sum in an SQL statement and if not how can
i return all this info in one go.

Clear as mud eh ??

Thanks

Reggiee

 

[Michel Walsh]

Hi,



technically, it is possible to make more than one sum, per group, such as:


SELECT store, productID, SUM(qty), SUM(qty*unitPrice)
FROM myTable
GROUP BY store, productID



would sum the quantity, and the value, for each item, each store.


Hoping it may help,
Vanderghast, Access MVP