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?