How can I find duplicate orders per customer?

Question

How can I query my SQL database and find all the duplicate orders where a customer ordered something more than once?

Answer 1

Not a super clear question, but I get the gist of it. I don't know what your database looks like, but your query would look something like this:

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

Answer 2

SELECT customerId, productId, count(productId) 
FROM CustomerOrders 
GROUP BY customerId 
HAVING count(productId) > 1

Answer 3

You can use a Select Distinct for it.

Answer 4

A platform, version would help. So would a sample table. So here is a sample orders table:

SQL> select * from orders;

   CUST_ID   ORDER_ID ORDER_DAT
---------- ---------- ---------
     1     1 25-FEB-09
     1     2 24-FEB-09
     1     3 23-FEB-09
     2     4 24-FEB-09
     2     5 23-FEB-09
     2     6 22-FEB-09
     3     7 23-FEB-09
     9     8 22-FEB-09
     9     9 21-FEB-09
     4    10 22-FEB-09
     4    11 21-FEB-09
     4    12 20-FEB-09
     5    13 21-FEB-09
     5    14 20-FEB-09
     5    15 19-FEB-09
     6    16 20-FEB-09
    11    17 19-FEB-09
    10    18 18-FEB-09
     7    19 19-FEB-09
     7    20 18-FEB-09
     7    21 17-FEB-09
     8    22 18-FEB-09
     8    23 17-FEB-09
     8    24 16-FEB-09

24 rows selected.

The following select works on this table for a simple output:

  1* select cust_id, count(*) from orders group by cust_id having count(*) > 1
SQL> /

   CUST_ID   COUNT(*)
---------- ----------
     1     3
     2     3
     4     3
     5     3
     8     3
     7     3
     9     2

7 rows selected.