I need a query to count the total number of duplicates in a table, is there any way to do this?
If I have a table like this:
+------------+----------+
| item_name |quantity |
+------------+----------+
| Calculator | 89 |
| Notebooks | 40 |
| Pencil | 40 |
| Pens | 32 |
| Shirts | 29 |
| Shoes | 29 |
| Trousers | 29 |
+------------+----------+
I can't use SELECT COUNT(quantity) because it returns 2. (40 | 29)
How can I return 5? (40 | 40 | 29 | 29 | 29)
Using analytic functions:
WITH cte AS (
SELECT *, COUNT(*) OVER (PARTITION BY quantity) cnt
FROM yourTable
)
SELECT COUNT(*)
FROM cte
WHERE cnt > 1;
Your solution seems to work except that I think
SELECT COUNT(*)should be changed toSELECT SUM(cnt)I disagree, at least if you expect a count of 5 from your sample table.
Yep you're right I was looking at the table wrong