Sum more than once MS SQL2005

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
DeeVeeDee
Registered User
Posts: 172
Joined: 09 Apr 2010, 15:18

Sum more than once MS SQL2005

Post by DeeVeeDee »

Hi I have a small Integration 'CSV' file that i have to build but the query part is confusing me a tad bit.

I have the following fields in a Sales History table.

CUSTOMER, PERIOD, YEAR, DATE, ITEM NUMBER, SALES AMOUNT, QTY SOLD.

The data which is stored in the above table is transaction based so the will be more than one transactions in a day.

So now i need to build a Query which will be

CUSTOMER, YEAR, PERIOD, ITEM NUMBER, QTY SOLD , SALES AMOUNT
So it has to by Customer , by year by month by period , by item and Sum QTYSold on the ITEM number and sum Sales amount.

Is this possible without using a sub select ?
_̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡ ̡͌l̡̡̡
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Sum more than once MS SQL2005

Post by RuadRauFlessa »

Yes it is very possible
just try the below:

Code: Select all

SELECT 
  [CUSTOMER], 
  [PERIOD], 
  [YEAR], 
  [DATE], 
  [ITEM NUMBER], 
  [SALES AMOUNT], 
  SUM([QTY SOLD]) AS [QTY SOLD]
FROM
  [SALES HISTORY TABLE]
GROUP BY
  [CUSTOMER], 
  [YEAR], 
  [PERIOD], 
  [ITEM NUMBER],
  [SALES AMOUNT]
If the sales amount for the item stays the same then you can use it else you need to do a subquery of sorts. You can do away with a sub query by using apply depending on what you prefer.

Code: Select all

SELECT 
  [CUSTOMER], 
  [PERIOD], 
  [YEAR], 
  [DATE], 
  [ITEM NUMBER], 
  [SALES AMOUNT], 
  iTVF.QTY AS [QTY SOLD]
FROM
  [SALES HISTORY TABLE]  
CROSS
APPLY (
  SELECT 
    SUM([QTY SOLD]) AS QTY
  FROM
    [SALES HISTORY TABLE] AS [TMP]
  WHERE 
    [TMP].[CUSTOMER] = [CUSTOMER] 
    AND [TMP].[YEAR] = [YEAR], 
    AND [TMP].[PERIOD] = [PERIOD], 
    AND [TMP].[ITEM NUMBER] = [ITEM NUMBER]
  ) iTVF
GROUP BY
  [CUSTOMER], [YEAR], [PERIOD], [ITEM NUMBER]
Either one should actually do. Like I said it depends on your preference if you want to use the Apply keyword. If you have to bring back more than just the one col is actually makes sense else you have to do a nested select for each field you want to bring back which is just clumsy.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
DeeVeeDee
Registered User
Posts: 172
Joined: 09 Apr 2010, 15:18

Re: Sum more than once MS SQL2005

Post by DeeVeeDee »

Hey Man.
Thanks for the response.

Ive done this in the meanwhile and it seems to work.

Code: Select all

select  
YR,
PERIOD,
CUSTOMER,
ITEM ,
SUM(QTYSOLD)as QTYSOLD ,
SUM(FRETSALES) as FRETSALES,
SUM(FAMTSALES) as FAMTSALES
from OESHDT
WHERE CUSTOMER like 'CHAN%' and TRANDATE > '20100101' AND TRANDATE < '20100131' 
GROUP BY ITEM, CUSTOMER,PERIOD,YR

Code: Select all

WHERE TRANDATE > '20100101' AND TRANDATE < '20100131' 
Will be from the beginning of the month till the current date.

But the problem i have with the query is i cant include the Description of the ITEM and Customer Description because its in another table
and SQL gives an error probably because it wants to group by the Description aswell.

Should i just create the above as a view and then create a stored Proc query which links the above to the Customer Master ?
Is that how people do it , I'm just also tyring to have futre backups and all that in mind i dont like to clog our SQL Server with to many things.
_̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡ ̡͌l̡̡̡
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Sum more than once MS SQL2005

Post by RuadRauFlessa »

Well.... there are again a couple of ways. Seeing as the group by would then not work for you you could always go the inner select route. Creating a stored proc is not needed and neither is a view. In this case I would seriously advise you to go read up on joins and cross apply. Should help a lot. Coming to think of it if you do a proper join between your two tables the group by should not be a problem but it will make the group by more bulky.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
DeeVeeDee
Registered User
Posts: 172
Joined: 09 Apr 2010, 15:18

Re: Sum more than once MS SQL2005

Post by DeeVeeDee »

RuadRauFlessa wrote:Well.... there are again a couple of ways. Seeing as the group by would then not work for you you could always go the inner select route. Creating a stored proc is not needed and neither is a view. In this case I would seriously advise you to go read up on joins and cross apply. Should help a lot. Coming to think of it if you do a proper join between your two tables the group by should not be a problem but it will make the group by more bulky.
Thanks Googeling at this moment :mrgreen:
_̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡ ̡͌l̡̡̡
Post Reply