Grouping, counting and excluding based on column value
Although I've not a complete newbie in SQL or MySQL I notice that there's still quite a bit to learn. I cannot get my head around this one, after much trying, reading and searching. If you can give any pointers, I'd be grateful.
I have simplified the actual data and tables to the following.
Two tables are relevant: Staff, and Work. They contain data on staff in various projects.
Staff:
ID Name Unit
1 Smith Chicago
2 Clarke London
3 Hess Chicago
Work:
StaffID ProjectID
1 10
2 10
3 10
1 20
2 30
3 40
1 50
3 50
Goal:
To get grouped all those projects where there are staff from Chicago, with the count of all staff in that project.
Expected result:
Project Staff count
10 3
20 1
40 1
50 2
So the project 30 is not listed because its member(s) are not from Chicago.
My query below is obviously wrong. It counts only those project members who are from Chicago, not the whole project staff.
SELECT
work.projectID as Project, COUNT(*) as "Staff count"
FROM
staff
JOIN
work ON staff.ID=work.staffID
WHERE
unit="Chicago"
GROUP BY
work.开发者_C百科projectID;
I'd put the test for Chicago in a subselect.
Alternatively you can use a self-join, but I find the sub-select easier to understand.
SELECT
w.projectid as project
,COUNT(*) as `staff count`
FROM work w
INNER JOIN staff s ON (w.staffID = s.id)
WHERE w.projectID IN (
SELECT w2.projectID FROM work w2
INNER JOIN staff s2 ON (w2.staffID = s2.id AND s2.unit = 'Chicago'))
GROUP BY w.projectid
Remove the where clause and add a having clause which checks that at least one member of staff is from Chicago.
SELECT
work.projectID as Project, COUNT(*) as "Staff count"
FROM
staff
JOIN
work ON staff.ID=work.staffID
GROUP BY
work.projectID
HAVING
count(case unit when 'Chicago' then 1 end) > 0;
Finally: the result. Thanks again both @Johan and @a'r for your help, and @Johan for getting me on the right track (in my case).
I changed the sub-select to a derived table, and inner-joined this with the Work table on projectID.
SELECT
w.projectID AS project
,COUNT(*) AS `staff count`
FROM work w
INNER JOIN
(SELECT DISTINCT w2.projectID
FROM work w2
INNER JOIN staff s ON (w2.staffID = s.id AND s.unit = 'Chicago')) c
ON (w.projectID = c.projectID)
GROUP BY w.projectID
精彩评论