locked
How to calculate weight average RRS feed

  • Question

  • Hi guys, 

    I need to determine the weight average but I am struggling to find an idea:

    Create table #Forum (Customer varchar(10), quantity dec(6,2), price dec (6,2))
    Insert into #Forum values ('Alpha', 80, 2),('Alpha', 40, 11),('Beta', 110, 1.50),('Beta', 30, 12)

     Which is the average for Alpha for example. Not 6.5 because 80 qty was sold at $2 and 40 at $11.

    Any tips on that?

    Thanks

    Wednesday, September 6, 2017 10:28 AM

Answers

  • SELECT Customer,SUM(price*quantity)/SUM(quantity)WT FROM #Forum
    GROUP BY Customer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Wednesday, September 6, 2017 10:42 AM
    Answerer
  • Hi guys, 

    I need to determine the weight average but I am struggling to find an idea:

    Create table #Forum (Customer varchar(10), quantity dec(6,2), price dec (6,2))
    Insert into #Forum values ('Alpha', 80, 2),('Alpha', 40, 11),('Beta', 110, 1.50),('Beta', 30, 12)

     Which is the average for Alpha for example. Not 6.5 because 80 qty was sold at $2 and 40 at $11.

    Any tips on that?

    Thanks

    Please try below query - 

    select 
           Customer,
           SUM(price * quantity)/SUM(quantity) AS AvgWeight 
    FROM #Forum
    GROUP BY Customer



    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by Kapil.Kumawat Wednesday, September 6, 2017 10:55 AM
    • Marked as answer by DIEGOCTN Wednesday, September 6, 2017 12:36 PM
    Wednesday, September 6, 2017 10:55 AM

All replies

  • SELECT Customer,SUM(price*quantity)/SUM(quantity)WT FROM #Forum
    GROUP BY Customer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Wednesday, September 6, 2017 10:42 AM
    Answerer
  • Please try below

    Create table #Forum (Customer varchar(10), quantity dec(6,2), price dec (6,2))
    Insert into #Forum values ('Alpha', 1, 2),('Alpha', 2, 4),('Beta', 3, 9),('Beta', 4, 12)
    
    
    select customer,sum(price)/sum(quantity) as avg_price_per_quantity from #Forum group by customer
    
    drop table #Forum


    • Marked as answer by DIEGOCTN Wednesday, September 6, 2017 12:36 PM
    • Unmarked as answer by Jingyang Li Wednesday, September 6, 2017 1:40 PM
    Wednesday, September 6, 2017 10:43 AM
  • Hi guys, 

    I need to determine the weight average but I am struggling to find an idea:

    Create table #Forum (Customer varchar(10), quantity dec(6,2), price dec (6,2))
    Insert into #Forum values ('Alpha', 80, 2),('Alpha', 40, 11),('Beta', 110, 1.50),('Beta', 30, 12)

     Which is the average for Alpha for example. Not 6.5 because 80 qty was sold at $2 and 40 at $11.

    Any tips on that?

    Thanks

    Please try below query - 

    select 
           Customer,
           SUM(price * quantity)/SUM(quantity) AS AvgWeight 
    FROM #Forum
    GROUP BY Customer



    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Proposed as answer by Kapil.Kumawat Wednesday, September 6, 2017 10:55 AM
    • Marked as answer by DIEGOCTN Wednesday, September 6, 2017 12:36 PM
    Wednesday, September 6, 2017 10:55 AM