Trying to use a count column in the where part of a query

Question

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'.

Tim Biegeleisen · Answer 1 · 2016-07-04T05:34:29.097

2

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

edited Jul 04 '16 at 05:34

answered Jul 04 '16 at 05:17

Tim Biegeleisen

502,043
27
286
360

Shouldn't it be in a having clause, not a where clause? – ZLK Jul 04 '16 at 05:18
@ZLK It helps to read the question. Let's see whether this gets downvoted. – Tim Biegeleisen Jul 04 '16 at 05:18
1

I mean it should be `GROUP BY students.camID, campus.camName HAVING SUM(students.stuID) > 3`, rather than an aggregate in a where clause. – ZLK Jul 04 '16 at 05:21
1

YES! The second one works but I have to use `SUM(students.stuID)` to prevent an invalid column name. Thanks a lot – Tristan Jul 04 '16 at 05:26
@TimBiegeleisen: I get that the `WHERE` clause is (logically) evaluated before the `GROUP BY` but it seems to me that the `SELECT` and `HAVING` are evaluated within the same (logical) scope, so what's the (logical) reason for not allowing `studentCount` to be used in the `HAVING` clause? – onedaywhen Jul 07 '16 at 08:24
1

@onedaywhen I don't know the answer to this, and the answer is RDMBS-specific. I was curious myself, so I recently [asked this very question](http://stackoverflow.com/questions/38187913/performance-implications-of-allowing-alias-to-be-used-in-having-clause). – Tim Biegeleisen Jul 07 '16 at 08:26

score 0 · Answer 2 · answered Jul 04 '16 at 05:31

0

    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

answered Jul 04 '16 at 05:31

Hitesh Thakor

471
2
12

onedaywhen · Answer 3 · 2016-07-07T08:26:33.237

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]

Trying to use a count column in the where part of a query

3 Answers3

Linked