Sum of Sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sum of Sums

Author

Topic

slboytoy
Starting Member

30 Posts

Posted - 2005-01-25 : 16:15:23

I have a query that will sum of the net price on by each line item. Can I sum up everything, by quotenumber? Instead of two quieries, bring it into one?

[CODE]SELECT HDR.PlantNumber, HDR.QuoteNumber,
(ITM.NetUnitPrice + SUM(ISNULL(ITX.Adder, 0))) * ITM.Quantity * ITM.DiscountMultiplier AS QuoteNetValue
FROM DataWarehouse.dbo.QuoteITX ITX RIGHT OUTER JOIN
DataWarehouse.dbo.QuoteITM ITM ON ITX.QuoteNumber = ITM.QuoteNumber AND ITX.PlantNumber = ITM.PlantNumber AND
ITX.ItemNumber = ITM.LineItem RIGHT OUTER JOIN
DataWarehouse.dbo.QuoteHDR HDR ON ITM.PlantNumber = HDR.PlantNumber AND ITM.QuoteNumber = HDR.QuoteNumber
WHERE (HDR.PlantNumber = '12' OR
HDR.PlantNumber = '18' OR
HDR.PlantNumber = '19' OR
HDR.PlantNumber = '22' OR
HDR.PlantNumber = '23')
GROUP BY HDR.PlantNumber, HDR.QuoteNumber, HDR.YourInquiry, ITM.NetUnitPrice, ITM.Quantity, ITM.DiscountMultiplier[/CODE]

Results
[PlantNumber, QuoteNumber, NetValue]
5, 700, $50
5, 700, $75
5, 700, $25
5, 700, $50
5, 701, $10
5, 701, $10
5, 701, $10

New Results
[PlantNumber, QuoteNumber, NetValue]
5, 700, $200
5, 701, $30

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-25 : 16:28:19

[code]
SELECT PlantNumber, QuoteNumber, SUM(QuoteNetValue)
FROM (
SELECT HDR.PlantNumber, HDR.QuoteNumber,
(ITM.NetUnitPrice + SUM(ISNULL(ITX.Adder, 0))) * ITM.Quantity * ITM.DiscountMultiplier AS QuoteNetValue
FROM DataWarehouse.dbo.QuoteITX ITX RIGHT OUTER JOIN
DataWarehouse.dbo.QuoteITM ITM ON ITX.QuoteNumber = ITM.QuoteNumber AND ITX.PlantNumber = ITM.PlantNumber AND
ITX.ItemNumber = ITM.LineItem RIGHT OUTER JOIN
DataWarehouse.dbo.QuoteHDR HDR ON ITM.PlantNumber = HDR.PlantNumber AND ITM.QuoteNumber = HDR.QuoteNumber
WHERE (HDR.PlantNumber = '12' OR
HDR.PlantNumber = '18' OR
HDR.PlantNumber = '19' OR
HDR.PlantNumber = '22' OR
HDR.PlantNumber = '23')
GROUP BY HDR.PlantNumber, HDR.QuoteNumber, HDR.YourInquiry, ITM.NetUnitPrice, ITM.Quantity, ITM.DiscountMultiplier
) a
GROUP BY PlantNumber, QuoteNumber
[/code]

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources