Reputation: 307
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
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
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