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

Get duplicate id's with same desc – Mysql

I have a table with two fields, ID and Description. I have many rows in said table. I want to know how many times an id with the same description is repeated.

For example:

ID | Description
23 | Key
24 | Desk
25 | Key
26 | Key
27 | Desk

And I want to make a sql query which I get the number of times that a Description is repeated, the id that are associated and the own Description.

How can I solve this?

The output that I want it follows like this:

ID | Description | Occurrences
23 | Key | 3
24 | Desk | 2
25 | Key | 3
26 | Key | 3
27 | Desk | 2

Sorry if I have not explained well. I want to know number of occurrences of the same Description with the ID’s.

Wish your answers. Bests regards to all.

Tags:

3

Answers


  1. 0

    if i understand correctly you can run this:

    Select id, Desc, count(*)
from yourtable
group by Desc
Having count(*) > 1

    This query will provide you the results of the times a Desc is in the specific table.

    Login or Signup to reply.
  2. 0

    You can also use a query like this. It also show the duplicate ids.

    Select count(*) AS cnt, `Desc`, GROUP_CONCAT(ID) as IDs
from yourTable
group by `Desc`
Having count(*) > 1;
    Login or Signup to reply.
  3. 0

    I would go with the GROUP_CONCAT() solution suggested above, but this will give the output you requested using COUNT() as a window function:

    SELECT *
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY Description) AS Occurrences
    FROM your_table
) t
WHERE Occurrences > 1
ORDER BY ID;

    If you want to see all descriptions, not just the duplicated ones, you can drop the use of the subquery:

    SELECT *, COUNT(*) OVER (PARTITION BY Description) AS Occurrences
FROM your_table
ORDER BY ID;

    The same can be achieved with a correlated subquery in the SELECT list:

    SELECT *, (SELECT COUNT(*) FROM your_table WHERE Description = t.Description) AS Occurrences
FROM your_table t
HAVING Occurrences > 1
ORDER BY ID;

    The HAVING clause can be dropped if you want to include the descriptions which are not duplicated.

    All three will produce the same output for the sample data provided:

    ID Description Occurrences
    23 Key 3
    24 Desk 2
    25 Key 3
    26 Key 3
    27 Desk 2

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search