Results 1 to 3 of 3
May 13, 2004, 02:46 #1
- Join Date
- Jan 2004
- 0 Post(s)
- 0 Thread(s)
Counting with different where conditions
I have tried searching for this, but it is really difficult to find an answer if you don't know the name for what you are trying to accomplish.
There are two tables, 'clients' and 'projects'. One client can have multiple projects, so the projects table has a foreign key using clientID. There are two types of projects. The project table has a column projectTypeID, which is either 1 or 2.
I want to select a client and know how many project they have of type 1, and how many projects of type 2. Can this be done without having multiple separate queries?
I have looked at subqueries but I'm not sure if it is what I need or how I should use them in this situation.
Here is my base query:
SELECT c.clientID, c.clientName, count(p.projectID) as projectCount FROM clients as c LEFT JOIN projects as p USING (clientID) WHERE c.clientID = $clientID GROUP BY c.clientID
WHERE c.clientID = $clientID AND p.projectTypeID = 1
Could anyone suggest how I can get both the number of type 1 and type 2 projects in one query?