SQL Count AS something WHERE something = value

Im getting an error  in my SQL that says that I've haven't declared a column when I already have in my count..

ERROR: Invalid column name 'counting'.

ideas and ways round the problem?
Ta!
(SELECT COUNT(schoolID) AS counting FROM Lessons WHERE register.txtSchoolID = Lessons.txtSchoolID ) AS counting
 
//then later...
 
WHERE counting > 1

Open in new window

area96Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
you cant use the alias in the where part

(SELECT COUNT(schoolID) AS counting FROM Lessons WHERE register.txtSchoolID = Lessons.txtSchoolID ) AS counting
 
//then later...
 
WHERE (SELECT COUNT(schoolID) AS counting FROM Lessons WHERE register.txtSchoolID = Lessons.txtSchoolID ) > 1
0
 
Geert GOracle dbaCommented:
use the having clause instead ...

SELECT COUNT(schoolID) AS counting FROM Lessons
WHERE register.txtSchoolID = Lessons.txtSchoolID
having count(shoolid) > 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.