sql中的平均数 [英] Average of numbers in sql
本文介绍了sql中的平均数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
创建 表#temp2
(
salespersonid int ,
orders int
)
插入 进入#temp2(salespersonid,orders)
选择 1 , 24
union 全部 选择 2 , 24
union all 选择 3 , 48
union 所有 选择 4 , 72
union 所有 选择 5 , 24
union 全部 选择 < span class =code-digit> 6 , 18
union < span class =code-keyword> all 选择 2 , 48
union 所有 选择 3 , 24
选择 * 来自#temp2
SELECT salespersonid,sum(orders)作为#temp的数字2
group by salespersonid
- 现在输出是
1 24
2 72
3 72
4 72
5 24
6 18
现在我需要平均值而不使用with子句。
而且我也不想使用像计数不同的id [即6]。
平均值=总和/数...我不想用这个...
我需要的是
SELECT salespersonid,sum(orders)as number from#temp2
group by salespersonid
我只需要使用AVG功能,group by和sum 。
- 首先我们按salespersonid进行分组
- 然后我们需要平均
-
解决方案
不确定我是否理解正确。但如果我是正确的,您想获得销售人员的平均销售订单数量。正确?如果是这样,你可以这样做:
SELECT
salespersonid AS SalesPersonId,
SUM(订单)AS编号,
AVG(订单)AS平均
FROM
#temp2
GROUP BY
salespersonid
- 我得到解决方案,
SELECT AVG( NUMBERS) AS FINALAVERAGE FROM
(
SELECT
SUM(订单) AS 数字
FROM
#temp2
GROUP BY
salespersonid
) Ť
create table #temp2
(
salespersonid int,
orders int
)
insert into #temp2(salespersonid,orders)
select 1,24
union all select 2,24
union all select 3,48
union all select 4,72
union all select 5,24
union all select 6,18
union all select 2,48
union all select 3,24
select * from #temp2
SELECT salespersonid,sum(orders) as numbers from #temp2
group by salespersonid
--so now the output is
1 24
2 72
3 72
4 72
5 24
6 18
Now i need the average without using with clause.
And also i dont want to use like count distinct id [that is 6].
Avg= sum/count.. i dont want to use this...
What i need is after
SELECT salespersonid,sum(orders) as numbers from #temp2
group by salespersonid
I need to use only AVG function , group by and sum.
--so first we are doing group by salespersonid
--then we need average
--
解决方案
Not sure if I understood correctly. But if I am correct you want to get the average number of sales orders made by a sales person. Correct? If so, you can do like this:
SELECT salespersonid AS SalesPersonId, SUM(orders) AS Numbers, AVG(orders) AS Average FROM #temp2 GROUP BY salespersonid
--I GOT THE SOLUTION,,
SELECT AVG(NUMBERS) AS FINALAVERAGE FROM ( SELECT SUM(orders) AS Numbers FROM #temp2 GROUP BY salespersonid ) T
这篇关于sql中的平均数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文