Query this table so that it counts the duplicate entries?

Question

0.00/5 (No votes)

See more:

Posted 3-Dec-19 6:55am

Heather14637354

Updated 3-Dec-19 7:31am

Add a Solution

2 solutions

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

SQL

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

SQL

SELECT   ID, NAM, WAGE
     ,   Duplicates = Count(*) - 1 -- subtract 1 for the original
FROM     @Table
GROUP BY ID, NAM, WAGE
HAVING   COUNT(*) > 1

Posted 3-Dec-19 7:32am

MadMyche

Comments

Maciej Los 3-Dec-19 13:45pm

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2019-12-03T07:20:00

Solution 1

To view the duplicates:

SQL

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:

SQL

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
;

Posted 3-Dec-19 7:20am

Richard Deeming

Comments

Heather14637354 3-Dec-19 13:29pm

Thanks so much! Can I ask what the "cte" does?

Richard Deeming 3-Dec-19 13:31pm

It's a "common table expression":
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL Server CTE Basics - Simple Talk[^]

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

+5Gosh darn it; you out-typed me

Maciej Los 3-Dec-19 13:44pm

5ed!