Home > Enterprise >  count distinct values while left joining
count distinct values while left joining

Time:04-09

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.

  • Related