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
CodePudding user response:
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
.