CODEWITHSUNDEEP
mysql
pypep278
pypep278

Reputation: 307

Calculating the AVG of a COUNT() in mysql

I am trying to issue the following query:

SELECT 
  AVG(`count`)
FROM
  (SELECT 
    COUNT(s_id) AS `count`
  FROM
    instructor,
    advisor 
  WHERE instructor.ID = advisor.i_id 
    AND dept_name = 'CIS' 
  GROUP BY dept_name) nested ;

to calculate the average of a counted value. However, the nested query returns to me a single row with the counted value = 2. This value 2 is a result of 2 rows of records that match the query.

Now when I apply the average function to it I get 2.0000. However the expected answer is 1.0000.

Any idea how I can get around to getting the expected value?

Upvotes: 1

Views: 7601

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

This is your query:

SELECT AVG(`count`)
FROM (SELECT COUNT(s_id) AS `count`
      FROM instructor join
           advisor 
           on instructor.ID = advisor.i_id 
      WHERE dept_name = 'CIS' 
      GROUP BY dept_name
    ) nested

You are selecting one department name and then grouping by that. The where clause means that you are only getting one row. So, the average is the same as the count. The average of one thing is the value of the thing.

If you want the average across all departments, you can remove the where:

SELECT AVG(`count`)
FROM (SELECT COUNT(s_id) AS `count`
      FROM instructor join
           advisor 
           on instructor.ID = advisor.i_id 
      GROUP BY dept_name
    ) nested;

Or, you don't really need the subquery:

SELECT count(*) / count(distinct dept_name)
FROM instructor join
     advisor 
     on instructor.ID = advisor.i_id ;

Upvotes: 5

Peter Kiss
Peter Kiss

Reputation: 9329

AVG(s_id) will calculate the average s_id so if the two rows has the id's of 1 and 3 it will result as 2.

Upvotes: 0

Related Questions