CODEWITHSUNDEEP
sql
ASGmail
ASGmail

Reputation:

How can I find duplicate orders per customer?

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

Upvotes: 2

Views: 2954

Answers (5)

OctaviaLo
OctaviaLo

Reputation: 1396

Use a group by with both user_id and product_id. Assuming your order table has columns user_id and product_id.

SELECT user_id, product_id, COUNT(*)
FROM orders
GROUP BY user_id, product_id
HAVING COUNT(*) > 1;

This gives the following table

user_id | product_id | count
---------+------------+-------
       6 |         36 |     2
      27 |         68 |     1
      31 |         39 |     1
      26 |         98 |     1

Postgres will first group by user_id, and then group by product_id.

Upvotes: 0

user60890
user60890

Reputation: 500

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.

Upvotes: 1

amit
amit

Reputation: 10872

You can use a Select Distinct for it.

Upvotes: -1

Dead account
Dead account

Reputation: 19960

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

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60498

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

Upvotes: 6

Related Questions