I have a table that looks like this:
people:
+-----+--------+-----+
| id | name | age |
+-----+--------+-----+
| 25 | Alpha | 30 |
| 113 | Beta | 21 |
| 10 | Test | 19 |
+-----+--------+-----+
and another like this:
table2:
+-----+-----------+--------------+
| id | company | candidate_id |
+-----+-----------+--------------+-
| 1 | Google | 10 |
| 36 | Microsoft | 113 |
| 137 | Google | 10 |
| 2 | ITCompany | 10 |
+-----+-----------+--------------+
I want to join people with table2 such that I can find the total number of DISTINCT companies corresponding with each unique person id. My end result should be something like this:
+-----+--------+-----------+
| id | name | companies |
+-----+--------+-----------+
| 10 | Test | 2 |
| 25 | Alpha | 0 |
| 113 | Beta | 1 |
+-----+--------+-----------+
How can I do the count for companies?
SELECT people.id, name, company
FROM people
LEFT JOIN reports on people.id = table2.people_id
You can group by people.id and count the distinct companies:
SELECT p.id, p.name,
COUNT(DISTINCT r.company) companies
FROM people p LEFT JOIN reports r
ON p.id = r.people_id
GROUP BY p.id;
I assume the id is the primary key of the table people.