SQL query to only yield multiple occurances-CodePudding

I am using mariadb and I have a table called links:

id | product_id | last_change
------------------------------
 1            1           xxx
 2            2           xxx
 3            5           xxx
 4            5           xxx

I want to find every object (3, 4 in this example) that occures more than once. Following this answer I tried:

SELECT product_id, COUNT(*) from links HAVING COUNT(*) > 1

But this results in the (adapted to this example) first row being shown and the total number of product_id occurrences:

product_id | COUNT(*)
---------------------
         1         4

I wanted to achieve a list of all items occuring more than once:

id | product_id | last_change
------------------------------
 3            5           xxx
 4            5           xxx

CodePudding user response：

An aggregation function without GROUP BY always results in only one row result as it aggregates all rows

So use a GROUP BY

SELECT product_id, COUNT(*) from links GROUP BY product_id HAVING COUNT(*) > 1

To see all entry with the count of the product_id , you can do following

SELECT product_id , last_change , Count_ 
FROM links  l1 
JOIN (SELECT product_id, COUNT(*) as Count_ from links GROUP BY product_id HAVING COUNT(*) > 1) l2 
ON l1.product_id = l2.product_id

CodePudding user response：

Try below statement

select id, product_id, count(product_id) 
from links
group by (product_id)
having count(product_id)> 1;