Sum of Sums

[slboytoy]

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?

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

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

[danfran]

..2 leaps in a single bound, basically, "No."

Assuming you're using SQL Sesrver, the way to do it is to put the results of your first query into a #temp table. Then, aggregate the results within the temp table.

Aggregates go only "one level deep". -Something we SQL guys learn early-on when faced with a challenge..

Anyway.. Here's yer answer...

create table #temp1 (plantnum int, quotenum int, netvalue money)

insert #temp1
select [your first query here]

-- return the "New Results"
select plantnum, quotenum, sum(netvalue) 
from #temp1
group by plantnum, quotenum

drop table #temp1

The above code would be contained within a stored procedure. If you're trying to do it with Access or purely on the ASP side, then you'll have to come up with something more creative.. (arrays or other kinds of recordset manipulation).

Cheers!

Dan

[r937]

i'm not sure i understand the question

your first query has

... GROUP BY HDR.PlantNumber, HDR.QuoteNumber, HDR.YourInquiry, ITM.NetUnitPrice, ITM.Quantity, ITM.DiscountMultiplier

but apparently this isn't summed up high enough, so why don't you just change the GROUP BY to sum up to the level that you want?

[slboytoy]

Sorry I took out some code to make it look more simple, and forgot to change the groupby part.

I am basically doing it in two folds right now (Just how you have it Danfran). I was just checking what the best pratice way is. Thanks again guys.