同一查询中count()的平均值 [英] Average on a count() in same query

问题描述

我目前正在从事一项作业，这需要我找到每个模块的平均资源数量.当前表如下:

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

因此，基本上，我正在尝试找出如何获取平均资源数量.唯一的 此处的相关测试数据是针对模块1的，模块1连接有3种不同的资源.但是我需要显示所有结果.

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.

这是我的代码:

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.

推荐答案

这是您正在执行的查询，使用的语法略显晦涩.

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;

您正在交叉连接表格，总共制作(6x6 =)36行，并将其压缩为4行，但是由于总数为36，结果是错误的.
这就是为什么您永远不要使用隐式联接的原因.

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.

将查询重写为:

SELECT AVG(a.rcount) FROM 
  (select count(*) as rcount 
   FROM ressursertiloppgave r
   GROUP BY r.ModulID) a

如果您希望单个行计数和平均值在底部，请执行以下操作:

If you want the individual rowcount and the 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

这篇关于同一查询中count()的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！