14,428,340 members
Rate this:
See more:
I have a table which looks like this:

ID | NAME | WAGE

1 | PAUL | 30
1 | PAUL | 30
2 | SARA | 40
2 | SARA | 40
3 | ISLA | 50
1 | PAUL | 30

I need to create an SQL query which will count the duplicate entries and give me a result of 3 (2 extra for Paul plus 1 extra for Sara).

What I have tried:

I've been attempting to do this using the UNION instruction, however I'm unclear as to how to create two separate queries to then use this instruction on.
Posted
Updated 3-Dec-19 8:31am

Rate this:

## Solution 1

To view the duplicates:
```SELECT
ID,
NAME,
WAGE,
Count(1) - 1 As Extras
FROM
YourTable
GROUP BY
ID,
NAME,
WAGE
HAVING
Count(1) > 1```
If you just want the total number of extras:
```WITH cte As
(
SELECT
Count(1) - 1 As Extras
FROM
YourTable
GROUP BY
ID,
NAME,
WAGE
HAVING
Count(1) > 1
)
SELECT
Sum(Extras) As TotalExtras
FROM
cte
;```
Heather14637354 3-Dec-19 13:29pm

Thanks so much! Can I ask what the "cte" does?
Richard Deeming 3-Dec-19 13:32pm

You could also write the query without it:
```SELECT
Sum(Extras) As TotalExtras
FROM
(
SELECT
Count(1) - 1 As Extras
FROM
YourTable
GROUP BY
ID,
NAME,
WAGE
HAVING
Count(1) > 1
) As cte
;```

I just think the CTE version looks tidier. :)

+5 Gosh darn it; you out-typed me
Maciej Los 3-Dec-19 13:44pm

5ed!
Rate this:

## Solution 2

There are a couple of queries I would be looking at; neither one of which will use the `UNION` operator.

The first one is going to be give you exactly what you are asking for by utilizing a CTE (Common Table Expression) being populated via a `SELECT DISTINCT` query
```DECLARE @Table TABLE ( ID INT, NAM VARCHAR(8), WAGE INT )

INSERT @Table VALUES (1, 'PAUL', 30), (1, 'PAUL', 30), (2, 'SARA', 40), (2, 'SARA', 40), (3, 'ISLA', 50), (1, 'PAUL', 30)

; WITH Dupes AS ( SELECT Distinct Id, Nam, Wage FROM @Table )

SELECT DuplicateRows =
(SELECT Count(*) FROM @Table)
- (SELECT Count(*) FROM Dupes)```
The second query may be of use also; as it will identify which records are being duplicated, and how many duplicates there are. This will be done via the `GROUP BY ... HAVING` clauses
```SELECT   ID, NAM, WAGE
,   Duplicates = Count(*) - 1 -- subtract 1 for the original
FROM     @Table
GROUP BY ID, NAM, WAGE
HAVING   COUNT(*) > 1```
Maciej Los 3-Dec-19 13:45pm

5ed!