[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Count AS something WHERE something = value

Posted on 2009-12-16
2
Medium Priority
?
215 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:area96
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26062549
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 26062604
use the having clause instead ...

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question