Count multiple appearances in DISTINCT statement with two parameters?
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 assign开发者_如何学Pythoned.
Thx in advance for any help!
How about this?
SELECT company_id, COUNT(DISTINCT person_id)
FROM mytable
GROUP BY company_id
HAVING COUNT(DISTINCT person_id) > 1
SELECT
company_id
FROM
mytable
GROUP BY
company_id
HAVING
COUNT(person_id) > 1
ORDER BY
company_id
精彩评论