Performing A Query On Field Generated From Sub Query

Viewed 32

I have two tables, Clients and Jobs.

I am doing a report on clients, and as part of the query, I also return a count of how many jobs we have done for each client. The code is as follows:

SELECT * ,  
(SELECT COUNT(*) FROM Jobs WHERE ClientID = Clients.ID) AS JobsCount
FROM Clients  

I now need to modify this to allow conditional queries based on the JobsCount, such as "return all clients with more than 100 jobs". Simply adding a where clause on JobsCount doesnt seem to work - Invalid column name 'JobsCount'.

How can i query on a column that is generated through a sub query?

3 Answers
Related