Click here to Skip to main content
14,391,964 members
Rate this:
Please Sign up or sign in to vote.
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:
Please Sign up or sign in to vote.

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
;
   
Comments
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. :)
MadMyche 3-Dec-19 13:34pm
   
+5 Gosh darn it; you out-typed me
Maciej Los 3-Dec-19 13:44pm
   
5ed!
Rate this:
Please Sign up or sign in to vote.

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
   
Comments
Maciej Los 3-Dec-19 13:45pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100