MySQL 同一查询中 count() 的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7893631/
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
Average on a count() in same query
提问by Coss
I'm currently working on an assignment which requires me to find the average on the number of resources for each module. The current table looks like this:
我目前正在完成一项任务，该任务要求我找到每个模块的平均资源数量。当前表如下所示：
ResourceID ModulID
1 1
2 7
3 2
4 4
5 1
6 1
So basically, I'm trying to figure out how to get the average number of resources. The only relevant test data here is for module 1, which has 3 different resources connected to it. But I need to display all of the results.
所以基本上，我试图弄清楚如何获得平均资源数量。这里唯一相关的测试数据是模块 1，它有 3 个不同的资源与之相连。但我需要显示所有结果。
This is my code:
这是我的代码：
select avg(a.ress) GjSnitt, modulID
from
(select count(ressursID) as ress
from ressursertiloppgave
group by modulID) as a, ressursertiloppgave r
group by modulID;
Obviously it isn't working, but I'm currently at loss on what to change at this point. I would really appreciate any input you guys have.
显然它不起作用，但我目前不知道此时要改变什么。我真的很感激你们的任何意见。
回答by Johan
This is the query you are executing, written in a slightly less obtuse syntax.
这是您正在执行的查询，以稍微不那么钝的语法编写。
SELECT
avg(a.ress) as GjSnitt
, modulID
FROM
(SELECT COUNT(ressursID) as ress
FROM ressursertiloppgave
GROUP BY modulID) as a
CROSS JOIN ressursertiloppgave r <--- Cross join are very very rare!
GROUP BY modulID;
You are cross joining the table, making (6x6=) 36 rows in total and condensing this down to 4, but because the total count is 36, the outcome is wrong.
This is why you should never use implicit joins.
您正在交叉加入表格，总共制作 (6x6=) 36 行并将其压缩为 4，但由于总数为 36，因此结果是错误的。
这就是为什么你永远不应该使用隐式连接。
Rewrite the query to:
将查询重写为：
SELECT AVG(a.rcount) FROM
(select count(*) as rcount
FROM ressursertiloppgave r
GROUP BY r.ModulID) a
If you want the individual rowcount andthe average at the bottom do:
如果您想要单个行数和底部的平均值，请执行以下操作：
SELECT r1.ModulID, count(*) as rcount
FROM ressursertiloppgave r1
GROUP BY r1.ModulID
UNION ALL
SELECT 'avg = ', AVG(a.rcount) FROM
(select count(*) as rcount
FROM ressursertiloppgave r2
GROUP BY r2.ModulID) a
