SQL query to only yield multiple occurances

Question

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

nbk · Accepted Answer · 2022-07-15T11:11:09.660

2

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 l1.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

edited Jul 15 '22 at 11:11

answered Jul 14 '22 at 12:25

nbk

45,398
8
30
47

This brings me much closer to my desired output - but only shows the number of the appearances. I want to see a list with all entries that are produced from the output. In my example your code shows ONE line for every multi-occurance, but I want to see ALL of the lines. – xtlc Jul 14 '22 at 12:33
1

Hm, ok, i added a query that gives the wanted result – nbk Jul 14 '22 at 13:11
I get an error message: `Column 'product_id' in field list is ambiguous` trying your snippet. – xtlc Jul 15 '22 at 08:50
1

yes, you have to add a identifier. see changed answer. – nbk Jul 15 '22 at 11:21
This was exactly what was missing. Thank you sir! – xtlc Jul 15 '22 at 12:51

score 0 · Answer 2 · edited Jul 14 '22 at 13:15

0

Try below statement

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

edited Jul 14 '22 at 13:15

Ankit Bajpai

13,128
4
25
40

answered Jul 14 '22 at 12:33

Maitreyee_stack

16
1
4

Thanks for your answer. Despite this being the exact same answer @nbk posted earlier, it is also not the exact answer to my question. My question aims for a result where every occurrence is listed, not a list with the counts of the occurrences. – xtlc Jul 14 '22 at 12:41

SQL query to only yield multiple occurances

2 Answers2