我需要对一列求和并除以另一列中的非重复值
Column A Column B
10 James
20 Phill
02 James
10 Bill
23 Bill
14 James
我的答案需要是 A 列 (79) 除以不同的詹姆斯 (3)、菲尔 (1)、比尔 (2) 的总和
我的答案应该是:
James 26.333
Phil 79
Bill 39.5
我试过了:
select sum(column A)/distinct (column B)
金额列求和并按名称分组。
选择名称,总和(金额)作为 AMT 从表名称分组按名称分组
我的结果:
Name Amt
Bill 33
James 26
Phill 20
你的问题变了。我会更新答案...
SELECT (SELECT SUM(Amount) FROM TableName) / COUNT(Name) AS CalcAmount, Name
FROM TableName GROUP BY Name
结果:
CalcAmount Name
39 Bill
26 James
79 Phill
select t1.sum_number/ count(t2.name), t2.name
from (
select sum(number) as sum_number
from t) as t1
join t as t2
join t as t3
on t2.name = t3.name
and t2.number = t3.number
group by t2.name
你可以在这里摆弄它
试试这段代码
SELECT C2, MAX(A.SUMC1)/COUNT(C2) CalcAmount
FROM TableA,
(SELECT SUM(C1) SUMC1 FROM TableA) A
GROUP BY C2
它工作了,我签入了SQLFiddle http://sqlfiddle.com/#!3/54794/6/0
试试这个,假设:表名称 = 测试,A 列 = 销售额,B 列 = 名称
SELECT a.name,
(SELECT SUM(b.sales) / COUNT(a.NAME) FROM test AS b)
FROM test AS a
GROUP BY NAME
当你的DBMS支持"窗口函数"时,你可以做:
SELECT ColB, CAST(SUM(SumA) OVER () AS FLOAT)/cnt CalcAmount
FROM
(
SELECT
ColB,
SUM(ColA) AS SumA,
COUNT(*) AS cnt
FROM TableA
GROUP BY ColB
) AS dt
如果 ColA 是一个整数,你需要将 CAST(SUM(SumA) 在 () 上作为浮点数/十进制)以避免结果的缩龙
您可以使用窗口函数执行此操作:
select columnB,
sum(sum(columnA)) over ()) / count(columnB) as YourAverage
from t
group by columnB;
这是SQL小提琴。