Trying to use the count column in that part of the query

Question

There are 2 tables called

Students

• stuID
• camID FK

Campus

• camID PK
• camName

I am trying to find campuses with more than 4 students, which include camName, camID, (number of students)

This is what I got so far

SELECT students.camID, campus.camName, SUM(students.stuID) as [count] FROM students JOIN campus ON campus.camID = students.camID WHERE [count] > 3 GROUP BY students.camID, campus.camName ORDER BY [count] 

All this causes me, although this is an error where the "Invalid column name" is counted.

Answer 1

You cannot use a column alias in a WHERE because a WHERE is evaluated before the alias is even created. You also cannot use an alias in a HAVING .

 SELECT students.camID, campus.camName, COUNT(students.stuID) as studentCount FROM students JOIN campus ON campus.camID = students.camID GROUP BY students.camID, campus.camName HAVING COUNT(students.stuID) > 3 ORDER BY studentCount 

Answer 2

  SELECT [t0].* FROM campus AS [t0] INNER JOIN (SELECT COUNT(*) AS [value], [t1].camID FROM students AS [t1] GROUP BY [t1].camID ) AS [t2] ON [t0].camID = [t2].camID WHERE [t2].[value] > 3 

Answer 3

The first SQL products did not support views, so HAVING was invented. But now we have views, so we no longer need HAVING , and indeed, this can be confusing (note that legacy functionality is never removed from the SQL standard):

 SELECT * FROM ( SELECT students.camID, campus.camName, SUM(students.stuID) as [count] FROM students JOIN campus ON campus.camID = students.camID GROUP BY students.camID, campus.camName ) AS DT1 WHERE [count] > 3 ORDER BY [count]