SQL Query to count rows that have a common column value based on another column value

Question

I have a single MySQL table with this data:

CREATE TABLE job_history(
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee VARCHAR(50),
  company VARCHAR(50)
);

INSERT INTO job_history(employee, company) VALUES
('John', 'IBM'),
('John', 'Walmart'),
('John', 'Uber'),
('Sharon', 'IBM'),
('Sharon', 'Uber'),
('Matt', 'Walmart'),
('Matt', 'Starbucks'),
('Carl', 'Home Depot');

SELECT * FROM job_history;
+----+----------+------------+
| id | employee | company    |
+----+----------+------------+
|  1 | John     | IBM        |
|  2 | John     | Walmart    |
|  3 | John     | Uber       |
|  4 | Sharon   | IBM        |
|  5 | Sharon   | Uber       |
|  6 | Matt     | Walmart    |
|  7 | Matt     | Starbucks  |
|  8 | Carl     | Home Depot |
+----+----------+------------+

Here's the corresponding SQL Fiddle

I want to create a SQL query to count the number of common companies between a given employee and other employees on the table.

For example, if I wanted to target employee 'John', I expect this result:

Sharon: 2
Matt: 1
Carl: 0

because Sharon has 2 common companies with John (IBM and Uber), Matt has 1 common company with John (Walmart), and Carl has 0 companies common with John.

How can I do that?

Go on. Try something.

Strawberry
– Strawberry

2018-02-11 18:24:12 +00:00
Commented Feb 11, 2018 at 18:24 — Strawberry
– Strawberry, Commented Feb 11, 2018 at 18:24

Gordon Linoff · Accepted Answer · 2018-02-11 18:41:27Z

2

First, you need a left join -- because you want all employees even those with no companies in common. Second, group by to get the count:

select jh.employee, count(jh_john.company) as num_in_common
from job_history jh left join
     job_history jh_john
     on jh_john.company = jh.company and
        jh_john.employee = 'John'
where jh.employee <> 'John'
group by jh.employee;

Note: If there could be duplicates in the table, then use count(distinct) rather than count().

answered Feb 11, 2018 at 18:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josh Withee · Accepted Answer · 2018-02-11 18:36:04Z

0

Try this:

SELECT jh2.employee, count(*)
FROM job_history jh1, job_history jh2
WHERE jh1.company = jh2.company
AND jh1.employee = 'John'
AND jh2.employee <> 'John'
GROUP BY jh2.employee

And if you want them in order from most companies in common to least, add this to the end of the query:

ORDER BY count(*) DESC

answered Feb 11, 2018 at 18:36

Josh Withee

11.6k4 gold badges53 silver badges69 bronze badges

1 Comment

rtorres Over a year ago

This misses zero-count employees. In the case described above, it doesn't print out 'Carl'. See corresponding SQL Fiddle.

Prabhath Amaradasa · Accepted Answer · 2018-02-11 18:41:34Z

0

-- This will give the count with matching one

SELECT
employee, COUNT(company)
FROM job_history  WHERE company  IN
(
SELECT
company
FROM job_history  WHERE employee ='John' -- parameter
) AND employee <> 'John'  -- parameter
GROUP BY employee

-- Adding count with all employees  

SELECT DISTINCT 
ac.employee,
 COALESCE(mc.JobCount, 0)  AS JobCount
FROM job_history  AS ac LEFT OUTER JOIN
(
SELECT
employee, COUNT(company) AS JobCount
FROM job_history  WHERE company  IN
(
SELECT
company
FROM job_history  WHERE employee ='John'  -- parameter
) AND employee <> 'John'   -- parameter
GROUP BY employee
 ) AS mc ON mc.employee  = ac.employee
 WHERE ac.employee <> 'John'   -- parameter

answered Feb 11, 2018 at 18:41

Prabhath Amaradasa

5032 silver badges7 bronze badges

Comments

marc_s · Accepted Answer · 2018-02-11 19:32:04Z

0

select 
    count(*) as comon_companies, b.employee 
from 
    job_history a
inner join  
    job_history b on a.company = b.company
where 
    a.employee <> b.employee
    and a.employee = 'sharon'
group by 
    b.employee

Use this query to get your desired result. You only need to replace a.employee value in where clause to input name of your target employee

edited Feb 11, 2018 at 19:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 11, 2018 at 18:49

Khan M

4154 silver badges21 bronze badges

1 Comment

rtorres Over a year ago

This also misses the zero-count results as shown here.

cdaiga · Accepted Answer · 2018-02-11 21:33:11Z

0

How can I do that?

Do a self join of the data on different employees, and company, then group the result on the employee and count the rows.

 SELECT B.employee, COUNT(A.company) FROM
 (SELECT  *  FROM JOB_HISTORY WHERE employee='John') A RIGHT JOIN
 (SELECT  *  FROM JOB_HISTORY WHERE employee<>'John') B
 ON A.company=B.company
 GROUP BY B.employee
 ORDER BY COUNT(A.company) DESC;

edited Feb 11, 2018 at 21:33

answered Feb 11, 2018 at 18:52

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

2 Comments

rtorres Over a year ago

Not sure why, but when I run this I get "Sharon: 3".

cdaiga Over a year ago

I forgot to group the result by employee. I just updated the query.

Collectives™ on Stack Overflow

SQL Query to count rows that have a common column value based on another column value

5 Answers 5

Comments

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related