Count multiple appearances in DISTINCT statement with two parameters?

Question

Dear all,

I am trying to filter (or count) companies with multiple contact persons from a table containing a company_id and a person_id. Currently I just do this:

SELECT DISTINCT company_id,person_id FROM mytable GROUP BY company_id ORDER BY company_id

as well as

SELECT DISTINCT company_id FROM mytable

The first query returns a couple of rows more. Hence it is obvious that there are companies with multiple contact persons. From the different row count between the two queries I can even tell how many of them. Though I´d like to know how I can select exactly those companies that have more than one person_id assigned.

Thx in advance for any help!

Answer 1

How about this?

SELECT company_id, COUNT(DISTINCT person_id)
FROM mytable
GROUP BY company_id
HAVING COUNT(DISTINCT person_id) > 1

Answer 2

SELECT
   company_id
FROM
   mytable
GROUP BY
   company_id
HAVING
   COUNT(person_id) > 1
ORDER BY
   company_id