Lesson 25 / 37 · Grouping

Finding duplicates with GROUP BY and HAVING

Group by the columns that should be unique, count, and keep the groups with more than one row. This is the standard way to find duplicates.

The query

SELECT customer_id, amount, count(*) AS times
FROM orders
GROUP BY customer_id, amount
HAVING count(*) > 1

Try grouping by customer_id alone, removing amount from SELECT and GROUP BY: 6 customers have more than one order.

Next: DISTINCT is GROUP BY without aggregates · All lessons