Trying to use a count column in the where part of

问题:

There is 2 tables called

Students

• stuID
• camID FK

Campus

• camID PK
• camName

I am trying to find the campuses with more than 4 students that include the 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 gets me though is a error that 'Invalid comlumn name 'count'.

回答1:

You can't use a column alias in a WHERE clause, because the WHERE clause is evaluated before the alias is even created. You also can't use an alias in the HAVING clause.

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

回答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

回答3:

The first SQL products didn't support derived tables, so HAVING was invented. But now we do have derived tables, so we no longer need HAVING and indeed it can cause confusion (note 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]