mysql - 多行计算

Question

我有以下输入表:

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

我需要计算库存值(value)数量的加权平均价格。例如，所有选定行的库存*价格除以总数。库存数量。从数学角度来说，

((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

如何扩展/修改我的 select 语句以获得以下输出:

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

Answer 1

您不能使用 (Inventory*Price)/sum(Inventory)，因为您没有按 Inventory 列进行分组。您只能使用 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