Mysql only showing rows with 2 or more values in a column-CodePudding

I have a query results that is similar to the following:

Part ID	Company
102	ABC Corp
102	XYZ Corp
103	ABC Corp
104	EFG Corp
104	HIJ Corp

This table is created with a simple query such as SELECT part_id, company FROM MyTable

However, I only want to return rows where the part ID number occurs more than once, i.e. I want the '103' part ID row taken out, as it has only one listing.

I tried using COUNT(*) but this returns '1' as the rows are always going to be unique. I want to remove rows where the COUNT of the total occurrences of a Part ID is only 1.

CodePudding user response：

try this

select * from
(SELECT part_id, company, count(pard_id) as count
FROM MyTable group by part_id,company) as a
where a.count>1;

CodePudding user response：

You can use

SELECT part_id, company
FROM   mytable
WHERE  part_id IN (SELECT part_id
                   FROM   mytable
                   GROUP  BY part_id
                   HAVING Count(*) > 1)

Output will be

Part ID Company
102 ABC Corp
102 XYZ Corp
104 EFG Corp
104 HIJ Corp