Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Group By Problems

    Hi I'm self taught trying to figure a simple query for the following table.

    SKU # || PricePaid || DatePurchased || QtyPurchased

    -------------------------------------------

    2349JF || 200.00 || 2010-23-11 || 2
    2349JF || 300.00 || 2010-01-11 || 5
    2349JF || 250.00 || 2011-01-23 || 6
    2349JF || 250.00 || 2011-32-11 || 1


    Simple table right? The problem is when I want to Query this data in such a way - Get the Average Price of an Item (SKU) for a YEAR, and total QTY of this item bough in this year

    Thus, returning:

    SKU || AveragePricePaid || YEAR || QTY
    ------------------------------------------


    Anyway help would be appreciated
    Last edited by Eric the Red; 05-14-11 at 19:24.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Eric the Red, what have you tried so far? And what would be your expected output?
    Also, which SQL Server are you using?
    Last edited by Stealth_DBA; 05-14-11 at 21:33.

  3. #3
    Join Date
    May 2011
    Location
    Boston, MA area
    Posts
    4

    Possible solution

    Hi,

    Here is some sql that should work. Based on the values in the price paid column, I assume this is the unit price paid. In other words, on 2010-01-11, 1500 was spent to buy 5 items at 300 each. If my assumption is not correct, I can provide a corrected solution.
    Code:
    select 
    	SKU, 
    	round( sum(PricePaid*qtyPurchased)/sum(qtyPurchased), 2) as 'AveragePaidPrice', 
    	datepart(year, datepurchased) as 'YEAR', 
    	sum(qtyPurchased) as 'QTY'
    from Purchases 
    group by 
    	SKU, 
    	datepart(year, datepurchased)
    I hope this helps.

    Andrew Zwicker - Visit A site to talk about SQL. | www.helpwithsql.com

  4. #4
    Join Date
    May 2008
    Posts
    97
    Thank you!

    Its nice that you showed me the 'Group by' in action! The price is not a divider of qty. In otherwords, if I buy 4 items at $200 - this means I spent 4x200=$800.

    Having said that, you have given me many ideas. Thank you

Posting Permissions

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