Which orders were keyed in twice?

Which orders were keyed in twice? A copy matches the original in every column except order_id, which is new for each row. Group by every other column, then keep the groups with more than one row.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Group by everything except the id

SELECT customer_id,
       store,
       product,
       revenue,
       coupon,
       order_date,
       COUNT(*) AS times
FROM orders
GROUP BY customer_id, store, product, revenue, coupon, order_date
HAVING COUNT(*) > 1

Result:

customer_id	store	product	revenue	coupon	order_date	times
C103	Abuja	Power bank	90		2026-03-12	2
C105	Lagos	Charger	30		2026-03-04	2

Step by step

One row for each set of copies.

SELECT customer_id, store, product, revenue, coupon, order_date, COUNT(*) AS times FROM orders GROUP BY customer_id, store, product, revenue, coupon, order_date HAVING COUNT(*) > 1

No join.

SELECT customer_id, store, product, revenue, coupon, order_date, COUNT(*) AS times FROM orders GROUP BY customer_id, store, product, revenue, coupon, order_date HAVING COUNT(*) > 1

HAVING COUNT(*) > 1.

SELECT customer_id, store, product, revenue, coupon, order_date, COUNT(*) AS times FROM orders GROUP BY customer_id, store, product, revenue, coupon, order_date HAVING COUNT(*) > 1

Group by every column except order_id.

SELECT customer_id, store, product, revenue, coupon, order_date, COUNT(*) AS times FROM orders GROUP BY customer_id, store, product, revenue, coupon, order_date HAVING COUNT(*) > 1

Practice

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

1. Which orders were keyed in twice? Show the details that repeat (every column except order_id) and how many times, as times.

SELECT customer_id, store, product, revenue, coupon, order_date, COUNT(*) AS times FROM orders GROUP BY customer_id, store, product, revenue, coupon, order_date HAVING COUNT(*) > 1

2. Which order_ids are the extra copies you'd delete, keeping the lowest order_id of each set?

WITH numbered AS (SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id, store, product, revenue, coupon, order_date ORDER BY order_id) AS rn FROM orders) SELECT order_id FROM numbered WHERE rn > 1

3. Which customers are listed twice, with the same name and city? Show name, city and times.

SELECT name, city, COUNT(*) AS times FROM customers GROUP BY name, city HAVING COUNT(*) > 1

4. Which customers placed more than one order on the same day? Show customer_id, order_date and orders.

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

5. How many extra copies are there in all? Show one number, extra_rows.

SELECT COUNT(*) - (SELECT COUNT(*) FROM (SELECT DISTINCT customer_id, store, product, revenue, coupon, order_date FROM orders) d) AS extra_rows FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed