Clever Geek Handbook

SQL Group with> 1

Perhaps the title was not the best one I could use to describe the problem. An example of the table structure I am dealing with is shown in the figure below. I need to write a query to pull all records for "Production" that have more than one record. So I would end up with LINUX UBUNTU 5.6 and LINUX REDHAT 7.8

Just returning duplicate PRODUCTION is easy and I can do it using a grouping having a count (*)> 1, but when it comes to returning duplicate production and matching columns, this is the problem I am facing.

Example table

+3


source to share


3 answers


returning duplicate PRODUCTION is easy and I can do it with grouping having count(*) > 1

This is a good start. Now use this list manufacture

to select the rest of the data:



SELECT *
FROM software
WHERE manufacture IN (
    -- This is your "HAVING COUNT(*) > 1" query inside.
    -- It drives the selection of rows in the outer query.
    SELECT manufacture
    FROM software
    GROUP BY manufacture
    HAVING COUNT(*) > 1
)
+4


source


try this:



  Select * from myTable
  Where Manufacture In
      (Select Manufacture
       from myTable
       Group By Manufacture
       Having count(*) > 1)
+3


source


Have you tried something like:

select p.manufacture, p.product, p.version
from (select manufacture, count(*)
    from products
    group by manufacture) as my_count
inner join products as p on p.manufacture = my_count.manufacture
where my_count > 1
0


source






More articles:


All Articles