using SUM()

[ricky]

I am working on the following query:
SELECT SOP30200.CUSTNMBR, SOP30200.CUSTNAME, IV00101.ITMSHNAM,
SOP30300.ITEMNMBR,
SOP30300.ITEMDESC, SOP30300.UNITPRCE, SOP30300.QTYTOINV, IV00105.LISTPRCE
FROM USGS.dbo.IV00101 IV00101, USGS.dbo.IV00105 IV00105, USGS.dbo.SOP30200
SOP30200,
USGS.dbo.SOP30300 SOP30300
WHERE IV00101.ITEMNMBR = SOP30300.ITEMNMBR AND IV00101.ITEMNMBR =
IV00105.ITEMNMBR
AND IV00105.ITEMNMBR = SOP30300.ITEMNMBR AND IV00105.CURNCYID =
SOP30200.CURNCYID
AND SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE =
SOP30300.SOPNUMBE
AND ((SOP30200.SOPTYPE='3')
AND (SOP30200.CUSTNMBR='INGRAM')
AND (SOP30200.DOCDATE BETWEEN '01/01/2009' AND '02/25/2009'))

i would like to know how many SOP30300.ITEMNMBR a customer purchased in a
given time period. i need to sum this SOP30300.QTYTOINV by SOP30300.ITEMNMBR
to get the total qty purchased for each product. i also need the total
amount in dollars (SOP30300.UNITPRCE * SOP30300.QTYTOINV).

can someone please help,
thanks,

[Mark Burgum]

well Sum() allows you to do such calculations. the only issue is what do you
mean by a given time period.

Sum() enables you to get a cursor of results by making use of the group by
clause giving you one record for each group returned by the sql and the sum
of the fields that where in that group as your sub totals.

so in your example grouping by your docdate field would return totals for
everyday that you have entries. however if you want weekly totals you are
going to have to convert that date into a week number.

hope this helps.

"ricky" <ri...@discussions.microsoft.com> wrote in message
news:89616599-B845-4E92...@microsoft.com...