比如表t1如下(amount=price*quantity,字段proname是商品名称):
proname type price quantity amount
A1 进仓 10 12 120
A2 采购退货 5 -10 -50
A2 采购退货 6 -8 -48
A1 进仓 0 20 0
A1 采购退货 9 -21 -189
A2 进仓 0 12 0
我想统计出来商品的进仓的数量是多少,赠品是多少(赠品以price=0),如下:
proname 进仓数量 赠品数量 采购退货 金额
A1 12 20 -21 -69
A2 12 -18 -98请问这个sql语句如何写?
proname type price quantity amount
A1 进仓 10 12 120
A2 采购退货 5 -10 -50
A2 采购退货 6 -8 -48
A1 进仓 0 20 0
A1 采购退货 9 -21 -189
A2 进仓 0 12 0
我想统计出来商品的进仓的数量是多少,赠品是多少(赠品以price=0),如下:
proname 进仓数量 赠品数量 采购退货 金额
A1 12 20 -21 -69
A2 12 -18 -98请问这个sql语句如何写?
select proname,
进仓数量=sum(case when type='进仓' and price!=0 then quantity else 0 end),
赠品数量=sum(case when type='进仓' and price=0 then quantity else 0 end),
采购退货=sum(case when type='采购退货' then quantity else 0 end),
金额=sum(amount)
from t1
group by proname
sum(case when type='采购退货' then quantity end) 退货数量,
sum(金额) 金额
from tb group by proname
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([proname] varchar(2),[type] varchar(8),[price] int,[quantity] int,[amount] int)
insert [t1]
select 'A1','进仓',10,12,120 union all
select 'A2','采购退货',5,-10,-50 union all
select 'A2','采购退货',6,-8,-48 union all
select 'A1','进仓',0,20,0 union all
select 'A1','采购退货',9,-21,-189 union all
select 'A2','进仓',0,12,0
---查询---
select proname,
进仓数量=sum(case when type='进仓' and price!=0 then quantity else 0 end),
赠品数量=sum(case when type='进仓' and price=0 then quantity else 0 end),
采购退货=sum(case when type='采购退货' then quantity else 0 end),
金额=sum(amount)
from t1
group by proname---结果---
proname 进仓数量 赠品数量 采购退货 金额
------- ----------- ----------- ----------- -----------
A1 12 20 -21 -69
A2 0 12 -18 -98(2 行受影响)
proname,
sum(case when type='进仓' and price<>0 then quantity else 0 end) as 进仓数量,
sum(case when type='进仓' and price=0 then quantity else 0 end) as 赠品数量,
sum(case when type='采购退货' then quantity else 0 end) as 采购退货,
sum(amount) as 金额
from
t1
group by
proname