CODEWITHSUNDEEP
sql
user2510809
user2510809

Reputation: 245

Can't use where clause with alias count function name

SELECT
   student_id
   ,COUNT(group_id) num
FROM StudentTable
WHERE num >= 2 
GROUP BY student_id 
ORDER BY num
desc
;

I keep getting the error: "num" invalid identifier. I don't understand why the where clause doesn't work with the alias name I give it. I already figured out the solution by using the "having" clause. I am just curious as to why the where clause doesn't work because In my mind it makes no sense as to why it doesnt work.

Upvotes: 0

Views: 163

Answers (2)

misterManager
misterManager

Reputation: 1174

you have to say either ORDER BY 2 or ORDER BY COUNT(group_id).

The query isn't "aware" of the column alias during the order by clause. The select is the VERY last thing to be run by the query engine.

The same thing applies to the WHERE clause.

If you're looking to filter on an aggregate function, using the HAVING clause is the best option. And even then, you have to say COUNT(group_id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You don't want a where clause. You want a having clause:

SELECT student_id, COUNT(group_id) num
FROM StudentTable
GROUP BY student_id 
HAVING num >= 2
ORDER BY num desc;

Some databases may not accept aliases in the having clause. In that case, you need to use a subquery or repeat the definition:

SELECT student_id, COUNT(group_id) num
FROM StudentTable
GROUP BY student_id 
HAVING COUNT(group_id) >= 2
ORDER BY COUNT(group_id) desc;

Upvotes: 3

Related Questions