SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Sum of Sums

  1. #1
    SitePoint Enthusiast
    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
    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
    SitePoint Addict danfran's Avatar
    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
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    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

Posting Permissions

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