SQL question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Reggiee - 31 Jul 2006 11:34 GMT

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

Reply to this Message

Michel Walsh - 31 Jul 2006 23:02 GMT

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

> Hi
>
[quoted text clipped - 19 lines]
>
> Reggiee

Reply to this Message