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