# Sum of Sums

• Jan 25, 2005, 15:14
slboytoy
Sum of Sums
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```
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
• Jan 25, 2005, 21:13
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.. :)

Code:

```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
• Jan 25, 2005, 22:11
r937
i'm not sure i understand the question