Question 21692 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search