SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Sum of Sums
-
Jan 25, 2005, 15:14 #1
- Join Date
- Oct 2004
- Location
- Owen Sound
- Posts
- 38
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
[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 #2
- Join Date
- Jan 2005
- Location
- New York City
- Posts
- 244
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
..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...
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
Cheers!
Dan
-
Jan 25, 2005, 22:11 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
-
Jan 31, 2005, 09:38 #4
- Join Date
- Oct 2004
- Location
- Owen Sound
- Posts
- 38
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks