1. ## 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

2. ..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

-- 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

3. i'm not sure i understand the question

... 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?

4. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•