Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Detecting duplicates with GROUP BY and HAVING COUNT(*) > 1

Grouping rows by a shared value and then filtering for groups where the count exceeds one is how you surface duplicates hiding in a dataset.

Example

Detecting Duplicate Retail Orders

SELECT order_id, customer_id, product_id, order_date, COUNT(*) AS occurrence_count
FROM orders
GROUP BY order_id, customer_id, product_id, order_date
HAVING COUNT(*) > 1

Result:

Step by step

1. Run the query without COUNT so you can see what the unfiltered values look like.

   SELECT order_id, customer_id, product_id, order_date, *
   FROM orders
   GROUP BY order_id, customer_id, product_id, order_date
   HAVING COUNT(*) > 1;

2. COUNT tells you how many rows match - perfect for sizing a table.

   SELECT order_id, customer_id, product_id, order_date, COUNT(*)
   FROM orders
   GROUP BY order_id, customer_id, product_id, order_date
   HAVING COUNT(*) > 1;

3. AS gives the COUNT result a friendly column name so the output reads cleanly.

   SELECT order_id, customer_id, product_id, order_date, COUNT(*) AS occurrence_count
   FROM orders
   GROUP BY order_id, customer_id, product_id, order_date
   HAVING COUNT(*) > 1;

Practice

Try each one in your head first, then open the answer.

SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1

SELECT product_id, COUNT(*) FROM products GROUP BY product_id HAVING COUNT(*) > 1

SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1

SELECT product_id, COUNT(*) FROM inventory GROUP BY product_id HAVING COUNT(*) > 1

SELECT customer_id, COUNT(*) FROM reviews GROUP BY customer_id HAVING COUNT(*) > 1