CODEWITHSUNDEEP
sqlsqlitegroup-byleft-joindistinct
user13067694
user13067694

Reputation:

count distinct values while left joining

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

Upvotes: 1

Views: 951

Answers (1)

forpas
forpas

Reputation: 164139

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.

Upvotes: 1

Related Questions