We are Borg We are Borg - 7 months ago 11
SQL Question

SQL : Getting duplicate rows along with other variables

I am working on Terradata SQL. I would like to get the duplicate fields with their count and other variables as well. I can only find ways to get the count, but not exactly the variables as well.

Available input

+---------+----------+----------------------+
| id | name | Date |
+---------+----------+----------------------+
| 1 | abc | 21.03.2015 |
| 1 | def | 22.04.2015 |
| 2 | ajk | 22.03.2015 |
| 3 | ghi | 23.03.2015 |
| 3 | ghi | 23.03.2015 |


Expected output :

+---------+----------+----------------------+
| id | name | count | // Other fields
+---------+----------+----------------------+
| 1 | abc | 2 |
| 1 | def | 2 |
| 2 | ajk | 1 |
| 3 | ghi | 2 |
| 3 | ghi | 2 |


What am I looking for :

I am looking for all duplicate rows, where duplication is decided by ID and to retrieve the duplicate rows as well.

All I have till now is :

SELECT
id, name, other-variables, COUNT(*)
FROM
Table_NAME
GROUP BY
id, name
HAVING
COUNT(*) > 1


This is not showing correct data. Thank you.

Answer

You could use a window aggregate function, like this:

SELECT *
FROM   (
        SELECT id, name, other-variables,
               COUNT(*) OVER (PARTITION BY id) AS duplicates
        FROM   users
       ) AS sub
WHERE  duplicates > 1