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:
This gives me the total number of projects for a given client. I could add a condition to the WHERE clause:
count(p.projectID) as projectCount
clients as c
LEFT JOIN projects as p USING (clientID)
c.clientID = $clientID
but this gives me either the number of projects of type 1 or type 2.
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?