mysql - Average on a count() in same query

Question

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.

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.

Answer 1

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.

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 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