Jay Roy
asked on
sql for duplicate rows in table
hi experts
I have table Customer with 4 columns and 100 rows of data.
Is there a handy sql to check if there are duplicate rows of data?
thanks
I have table Customer with 4 columns and 100 rows of data.
Is there a handy sql to check if there are duplicate rows of data?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<<SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC >>
looks good, i am trying to get a total sum of the duplicate rows
but this doesnt seem to work
SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC
thanks
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC >>
looks good, i am trying to get a total sum of the duplicate rows
but this doesnt seem to work
SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC
thanks
try this.
SELECT Column1,Column2,Column3,Column4,duplicate_rows,
SUM(duplicate_rows) OVER ( partition BY (SELECT 1)) sum_duplicate_rows
FROM (SELECT Column1,Column2,Column3,Column4,COUNT(Column4) AS duplicate_rows
FROM YourTable
GROUP BY Column1,Column2,Column3,Column4
HAVING COUNT(Column4) > 1) t1
ORDER BY duplicate_rows DESC
<somewhat redundant with the above comments>
>i am trying to get a total sum of the duplicate rows
Throw the whole thing in a subquery, and sum.
Replace the SUM with COUNT if you want distinct duplicate row columns.
SELECT SUM(a.duplicate_rows)
FROM (
SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC ) a
>i am trying to get a total sum of the duplicate rows
Throw the whole thing in a subquery, and sum.
Replace the SUM with COUNT if you want distinct duplicate row columns.
SELECT SUM(a.duplicate_rows)
FROM (
SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC ) a
ASKER
Any help with this question will be greatly appreciated
https://www.experts-exchange.com/questions/28148609/checksum-autogeneration.html
https://www.experts-exchange.com/questions/28148609/checksum-autogeneration.html
Thanks for the grade. Good luck with your project. -Jim
Open in new window
in the above code, replace the
<all your columns> with the columns list of your table
<your table> with your table name.