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

Mysql – How do I count duplicate rows in a table without using count function

Given the below,

WITH cte AS (
    SELECT employeeId, employeeName, 
        ROW_NUMBER() OVER (PARTITION BY employeeName ORDER BY employeeId asc) AS rn 
    FROM duplicateEmployee 
) 
SELECT * FROM cte WHERE rn > 1

I need to count the duplicate records without using count function from the duplicateEmployee table.
Please suggest me to how to modify the above query.

Tags:

2

Answers


  1. 0

    You should use COUNT() for this. Not allowing it is silly and makes this slower and more-complicated. But if you really want to:

    WITH cte AS (
    SELECT employeeId, employeeName, 
        ROW_NUMBER() OVER (PARTITION BY employeeName ORDER BY employeeId asc) AS rn 
    FROM duplicateEmployee 
) 
SELECT employeeName, MAX(rn) As DuplicateCount
FROM cte
GROUP BY employeeName
    Login or Signup to reply.
  2. 0

    Hello If you want to count the dups from your table without using count function but excluding the original entry in MySql you can do like this much shorter using sum(1) which here count every row:

        SELECT employeeName, sum(1) - 1 as dups_cnt
    FROM duplicateEmployee
group by employeeName;

    Hope it helps.

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

Back To Top
Search