CODEWITHSUNDEEP
mysql
Coss
Coss

Reputation: 453

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

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.

Upvotes: 30

Views: 82505

Answers (3)

Aristide
Aristide

Reputation: 3994

You could also divide the number of values (7) by the number of distinct values (4) :

SELECT count(*) / count(DISTINCT ResourceID)  
FROM   resurrectfromgrave

Upvotes: 0

WoodstockBrasil
WoodstockBrasil

Reputation: 11

I got the solution

SELECT AVG(counter) FROM ( SELECT COUNT(column to count) AS counter FROM table ) AS counter

Note that the nickname {counter} was added in SELECT COUNT and at the end of the inner SELECT

Upvotes: 1

Johan
Johan

Reputation: 76753

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

Upvotes: 51

Related Questions