sql - 如何计算 SQL 组中两个值的比率？

Question

我的输入是：

我想要的输出是：

我无法弄清楚如何计算属于该特定项目的两行的现金与优惠券数量的比率。

任何人都可以帮助我吗？

标签： sqlsql-server

Answer 1

例子

Select * 
      ,Ratio = convert(decimal(10,2),
               sum(case when [Payment_Mode]='Cash' then [Quantity]+0.0 end) over (Partition By [Item])
              /sum(case when [Payment_Mode]='Coupons' then [Quantity]  end) over (Partition By [Item])
              )
 From YourTable

退货

Item    Payment_Mode    Quantity    Ratio
Apples  Cash            20          2.00
Apples  Coupons         10          2.00
Grapes  Cash            45          15.00
Grapes  Coupons         3           15.00
Oranges Cash            300         20.00
Oranges Coupons         15          20.00

编辑 - 另一个选项是简单的加入和条件聚合

Select A.*
      ,B.Ratio
 From  YourTable A
 Join  (
        Select Item
              ,Ratio = sum(case when [Payment_Mode]='Cash'  then [Quantity]+0.0 end) /NullIF(sum(case when [Payment_Mode]='Coupons' then [Quantity] end),0)
         From  YourTable
         Group By Item
       ) B  on A.Item=B.Item