CODEWITHSUNDEEP
mysqlsqlsql-serverselecthana
Sangamesh Hs
Sangamesh Hs

Reputation: 1447

Calculation on multiple rows

I have the following Input table:

Article   Store   Supplier  NetPrice  Pieces   Sum   Inventory    Price           Cond
NL1234    N001    3100000   161,5       2       323   7           123,45           2,47
NL1234    N001    3100000   161,5       0       0     4           103,8            2,08
NL1234    N001    3100000   161,5       0       0     23          120,8            1,21

I need to calculate the weighted average of the price for the number of inventory value. For example, Inventory*price for all selected rows divided by the total no. of inventory number.Mathematically,

((7*123.45)+(4*103.8)+(120.8))/(34)

SELECT 
    Article,
    Store,
    Supplier,
    NetPrice,
    sum(Pieces) as "Pieces",
    sum(Sum) as "Sum",
    sum(Inventory) as "Inventory",
    (Inventory*Price)/sum(Inventory) as "Price",
    (Inventory*Cond)/sum(Inventory) as "Cond"
FROM
    table_name  
WHERE 
    "Article" = 'NL1234' 
GROUP BY 
    STORE,
    SUPPLIER,
    NetPrice,
    Article

How can I extend/modify my select statement to get the following output:

Article  Store   Supplier  NetPrice  Pieces  Sum  Inventory  Price    Cond  
NL1234   N001    3100000   161,5     2       323  34         119,35   1,57

Upvotes: 0

Views: 149

Answers (3)

Backs
Backs

Reputation: 24903

You cant use (Inventory*Price)/sum(Inventory) because you are not grouping by Inventory column. You only can use aggrigation functions like sum(Inventory).

SELECT 
    Article,
    SUM(Pieces) as "Pieces",
    SUM(Sum) as "Sum",
    SUM(Inventory) as "Inventory",
    SUM(Inventory * Price) / SUM(Inventory) as "Price",
    SUM(Inventory * Cond) / SUM(Inventory) as "Cond"
FROM
    table_name  
WHERE 
    "Article" = 'NL1234' 
GROUP BY 
    Article

Upvotes: 1

TTeeple
TTeeple

Reputation: 2989

Move the row totals into a CROSS APPLY, then use the result of that in the query like so:

SELECT Article,
       Store,
       Supplier,
       MAX(NetPrice),
       sum(Pieces) as "Pieces",
       sum(Sum) as "Sum",
       sum(Inventory) as "Inventory",
       T.TotalInvCost/sum(Inventory) as "Price",
       T.TotalInvCond/sum(Inventory) as "Cond"
FROM table_name
CROSS APPLY (
SELECT SUM(Inventory*Price) AS 'TotalInvCost'
,SUM(Inventory*Cond) AS 'TotalInvCond' 
FROM table_name
WHERE Article = 'NL1234'
) T
WHERE 
Article = 'NL1234' 
GROUP BY 
STORE,
SUPPLIER,
Article

Upvotes: 1

user170442
user170442

Reputation:

Remove columns you are not going to group by and get MAX of NetPrice.

However, it will work in MySQL but not on MSSQL as it would need to have Store and Supplier in GROUP BY clause. If you remove those columns from output you should get it working on MSSQL too.

SELECT 
    Article,
    Store,
    Supplier,
    MAX(NetPrice),
    sum(Pieces) as "Pieces",
    sum(Sum) as "Sum",
    sum(Inventory) as "Inventory",
    (Inventory*Price)/sum(Inventory) as "Price",
    (Inventory*Cond)/sum(Inventory) as "Cond"
FROM
    table_name  
WHERE 
    "Article" = 'NL1234' 
GROUP BY 
    Article

Upvotes: 0

Related Questions