case 语句统计平均值问题

学习到老死 2009-07-08 07:59:58
t1
列分别为编号,金额,类型,数目,货物编号
id money type num p_id
1 1.2 1 2 1
2 1.3 1 3 1
3 1.1 1 2 2
4 1.5 2 6 2
5 1.2 2 5 1

我用case语句判定是什么操作,然后想按照操作类型统计货品的平均价格(每笔单价和数量相乘的总和再除以总数量)
但是好像除以总数的语句提示不能为0
谢谢
...全文
58 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
学习到老死 2009-07-09
  • 打赏
  • 举报
回复
谢谢大家,问题解决了。
sum(money*num)/sum(case type=1 then num else ... end )
在被除数的地方如果else为0则会报错,因为被除数不能为0
(poofly)的方法启发了我,如果else不写0而写为null就可以了。
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 18 楼 feixianxxx 的回复:]
引用 17 楼 htl258 的回复:
SQL code---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1Ifnotobject_id('[t1]')isnullDroptable[t1]GoCreatetabl¡­

哈哈 我答过啦~

[/Quote]
我的是一条SQL
feixianxxx 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 17 楼 htl258 的回复:]
SQL code---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1Ifnotobject_id('[t1]')isnullDroptable[t1]GoCreatetabl¡­
[/Quote]
哈哈 我答过啦~
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1

If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1

-->SQL查询如下:
Select p_id,
avg1=max(case when type=1 then avgprice else 0 end),
avg2=max(case when type=2 then avgprice else 0 end)
From (
Select [type],[p_id],
sum([money]*[num])/case (select sum([num]) from t1) when 0 then 1 else (select sum([num]) from t1) end avgprice
From t1
Group by [type],[p_id]
) as t
Group by p_id

/*
p_id avg1 avg2
----------- --------------------------------------- ---------------------------------------
1 0.350000 0.333333
2 0.122222 0.500000

(2 行受影响)

*/
如果是除以总量的,这样.
feixianxxx 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 htl258 的回复:]
回复真快,一下被顶了下来.
[/Quote]
呵呵 不好意思
Tony
feixianxxx 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 woaimm123 的回复:]
多谢大家
money 是单价哈。
我想查询p_id 和其对应的均价
具体就是以下三列:
p_id avg1(type=1) avg2(type=2)
[/Quote]
或者这样 总数量 是 单种的
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert tb
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go

select p_id,MAX(avg1) as avg1 ,MAX(avg2) as avg2
from(
select p_id,
case when TYPE=1 then sum([money]*[num])/sum(num) end as avg1,
case when TYPE=2 then sum([money]*[num])/sum(num) end as avg2
from tb
group by p_id,type) t
group by p_id
/*
1 1.260000 1.200000
2 1.100000 1.500000
*/
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
回复真快,一下被顶了下来.
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 woaimm123 的回复:]
多谢大家
money 是单价哈。
我想查询p_id 和其对应的均价
具体就是以下三列:
p_id avg1(type=1) avg2(type=2)
[/Quote]
三列要求的结果在楼上.
feixianxxx 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 woaimm123 的回复:]
多谢大家
money 是单价哈。
我想查询p_id 和其对应的均价
具体就是以下三列:
p_id avg1(type=1) avg2(type=2)
[/Quote]
这样对么?我的程序里的总数量是全部的
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert tb
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
declare @sum int
set @sum=(select SUM(num) from tb )
select p_id,MAX(avg1) as avg1 ,MAX(avg2) as avg2
from(
select p_id,
case when TYPE=1 then sum([money]*[num])/@sum end as avg1,
case when TYPE=2 then sum([money]*[num])/@sum end as avg2
from tb
group by p_id,type) t
group by p_id
/*、
p_id avg1 avg2
1 0.350000 0.333333
2 0.122222 0.500000
*/
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1

If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1

-->SQL查询如下:
Select p_id,
avg1=max(case when type=1 then avgprice else 0 end),
avg2=max(case when type=2 then avgprice else 0 end)
From (
Select [type],[p_id],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type],[p_id]
) as t
Group by p_id

/*
p_id avg1 avg2
----------- --------------------------------------- ---------------------------------------
1 1.260000 1.200000
2 1.100000 1.500000

(2 行受影响)

*/
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 woaimm123 的回复:]
多谢大家
money 是单价哈。
我想查询p_id 和其对应的均价
具体就是以下三列:
p_id avg1(type=1) avg2(type=2)
[/Quote]
单价参见三楼的结果.
devilidea 2009-07-08
  • 打赏
  • 举报
回复
devilidea 2009-07-08
  • 打赏
  • 举报
回复

Select [type],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From test
Group by type
nalnait 2009-07-08
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 htl258 的回复:]
还是要这样的结果:

SQL code---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1Ifnotobject_id('[t1]')isnullDroptable[t1]GoCreatetable[t1]([id]int,[money]decimal(18,1),[type]int,[num]int,[p_id]int)Insert t1Select1,1.2,1,2,1unionallSelect2,1.3,1,3,1unionallSelect3,1.1,1,2,2unionallSelect4,1.5,2,6,2unionallSelect5,1.2,2,5,1Go--Select * from t1-->SQL查询如下:Select[type],[p_id],sum([money]*[num])/casesum([num])when0then1elsesum([num])end avgpriceFrom t1Groupby[type],[p_id]/*
type p_id avgprice
----------- ----------- ---------------------------------------
1 1 1.260000
2 1 1.200000
1 2 1.100000
2 2 1.500000

(4 行受影响)*/
[/Quote]
.
devilidea 2009-07-08
  • 打赏
  • 举报
回复
select sum(money*num)/sum(num)  from test group by type
学习到老死 2009-07-08
  • 打赏
  • 举报
回复
多谢大家
money 是单价哈。
我想查询p_id 和其对应的均价
具体就是以下三列:
p_id avg1(type=1) avg2(type=2)
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
[money]到底是做单价还是金额,烦请楼主出来说下.
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复
还是要这样的结果:

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1

If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1

-->SQL查询如下:
Select [type],[p_id],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type],[p_id]
/*
type p_id avgprice
----------- ----------- ---------------------------------------
1 1 1.260000
2 1 1.200000
1 2 1.100000
2 2 1.500000

(4 行受影响)
*/
htl258_Tony 2009-07-08
  • 打赏
  • 举报
回复

---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 20:08:23
---------------------------------
--> 生成测试数据表:t1

If not object_id('[t1]') is null
Drop table [t1]
Go
Create table [t1]([id] int,[money] decimal(18,1),[type] int,[num] int,[p_id] int)
Insert t1
Select 1,1.2,1,2,1 union all
Select 2,1.3,1,3,1 union all
Select 3,1.1,1,2,2 union all
Select 4,1.5,2,6,2 union all
Select 5,1.2,2,5,1
Go
--Select * from t1

-->SQL查询如下:
Select [type],sum([money]*[num])/case sum([num]) when 0 then 1 else sum([num]) end avgprice
From t1
Group by [type]
/*
type avgprice
----------- ---------------------------------------
1 1.214285
2 1.363636

(2 行受影响)
*/
SQL77 2009-07-08
  • 打赏
  • 举报
回复
SELECT 
TYPE,TOTAL/NULLIF(NUM,0) FROM
(SELECT SUM(TOTAL)TOTAL,TYPE,SUM(NUM)NUM FROM
(SELECT [money ]*NUM AS TOTAL,type ,NUM FROM TB ) GROUP BY TYPE)AS T1
??

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧