Derived table not recognised in where command

Question

I am new and still learning so please excuse my codes. I have googled and as last resort posting for help. Hope one of the senior programmers can help.

What I am trying to achieve: which course has below average number of students?

I am using SQL Server Management Studio v18.

My code does not work and I can not understand why derived table is not being accepted. I am aware that I can create another subquery and achieve it but still want to understand why derived table C in this case is not working?

Please help with explanation and suggest simplest way to achieve this query.

select 
    c.course, c.num_of_students
from
    (select 
         course, count(pname) as num_of_students
     from 
         Studies
     group by 
         COURSE) c
where 
    c.num_of_students < (select avg(c.num_of_students) from c);

I get this error:

Invalid object name 'c'

This invalid object name is highlighted in the WHERE clause line.

Data displays properly in the preview.

Data:

PNAME	INSTITUTE	COURSE	COURSEFEE
ANAND	SABHARI	PGDCA	4500
ALTAF	COIT	DCA	7200
JULIANA	BDPS	MCA	22000
KAMALA	PRAGATHI	DCA	5000
MARY	SABHARI	PGDCA	4500
NELSON	PRAGATHI	DAP	6200
PATRICK	PRAGATHI	DCAP	5200
QADIR	APPLE	HDCA	14000
RAMESH	SABHARI	PGDCA	4500
REBECCA	BRILLIANT	DCAP	11000
REMITHA	BDPS	DCS	6000
REVATHI	SABHARI	DAP	5000
VIJAYA	BDPS	DCA	48000

Answer 1

Derived tables have limited scope within a query and can only be referenced once after FROM. You have a few options, the best of which is a CTE:

WITH c AS (
    SELECT
        course, COUNT(pname) as num_of_students
    FROM Studies
    GROUP BY course
)
SELECT
    c.course, c.num_of_students
FROM c
WHERE
    c.num_of_students < (SELECT AVG(c.num_of_students) FROM c);

Additional options, although probably less desirable in this situation unless you need to use the results in your derived table in other queries, would be to define a temp table or a table variable:

Temp table:

DROP TABLE IF EXISTS #c;
SELECT
    course, COUNT(pname) as num_of_students
INTO #c
FROM Studies
GROUP BY course;

SELECT
    c.course, c.num_of_students
FROM #c c
WHERE
    c.num_of_students < (SELECT AVG(c.num_of_students) FROM c);

DROP TABLE #c;

Table variable:

DECLARE @c TABLE (
    course VARCHAR(100),
    num_of_students INT
);

INSERT @c (course, num_of_students)
SELECT course, COUNT(pname)
FROM Studies
GROUP BY course;

SELECT
    c.course, c.num_of_students
FROM @c c
WHERE
    c.num_of_students < (SELECT AVG(c.num_of_students) FROM c);