SQL 一个查询中总和的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1572831/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Avg of a Sum in one query
提问by Roch
I would like to know if I can get the average of a sum in one single SQL SERVER request,
我想知道是否可以在一个 SQL SERVER 请求中获得总和的平均值，
Have tried to do it with the following request but it doesn't work:
已尝试使用以下请求执行此操作，但不起作用：
SELECT t.client,
AVG(SUM(t.asset)) AS Expr1
FROM TABLE t
GROUP BY t.client
回答by Lukasz Lysik
I think your question needs a bit of explanation. If you want to take the sums grouped by t.clientyou can use:
我认为你的问题需要一些解释。如果要计算按以下分组的总和，t.client可以使用：
SELECT t.client, SUM(t.asset)
FROM the-table t
GROUP BY t.client
Then, if you want to take the average of this sume, just make:
然后，如果你想取这个 sume 的平均值，只需：
SELECT AVG(asset_sums)
FROM
(
SELECT t.client, SUM(t.asset) AS asset_sums
FROM the-table t
GROUP BY t.client
) as inner_query
You can't however group the outer query, because this will give you results like in the first query. The results from the inner query are already grouped by t.client.
但是，您不能对外部查询进行分组，因为这会给您提供与第一个查询类似的结果。来自内部查询的结果已按 分组t.client。
回答by Kalpesh Gohel
Its very simple
它非常简单
for ex.
例如。
SELECT t.client,
SUM(t.asset)/count(t.asset) AS average
FROM TABLE t
GROUP BY t.client
in "average" you will get average of "t.asset"
在“平均值”中，您将获得“t.asset”的平均值
回答by Gratzy
If you are trying to get the average assets of clients I think I would use at CTE. You can try if sql 2005 or greater
如果您想获得客户的平均资产，我想我会在 CTE 使用。如果 sql 2005 或更高版本，您可以尝试
EDIT: Took the second group by out as I think you want just one result here of average assets
编辑：将第二组排除在外，因为我认为您只需要平均资产的一个结果
With clientsum (client,assets)
as
(
SELECT CLIENT, SUM(asset) AS assets
FROM CLIENTTABLE
GROUP BY CLIENT
)
SELECT avg(assets) AS Expr1
FROM clientsum
