开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜