Finding orders that have been ordered multiple times (SQL query)

Question

I'm trying to find out which orders have been made more than once. This will help me identify the most common orders. Here is my orders table with some dummy data;

Table name: Orders

OrderID | ProductID | Quantity -------- | ----------| -------- 1 | 3 | 6 | 1 | 6 | 3 | 2 | 5 | 1 | 3 | 10 | 9 | 3 | 9 | 1 | 3 | 3 | 2 | 4 | 6 | 3 | 4 | 3 | 6 |

As you can see orders 1 and 4 are the same order I need a query to help me identify these orders.

Possible duplicate of [Finding duplicate values in a SQL table](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Aaron, Mar 13 '17 at 16:01
Why can two orders have the same ID? That seems confusing to me from a table design standpoint. How is uniqueness of orders determined ? — Hunter McMillen, Mar 13 '17 at 16:03
`As you can see orders 1 and 4 are the same order` ... this isn't clear to me at all. What makes them the same order? — Tim Biegeleisen, Mar 13 '17 at 16:06
They have the same productID and same quantity @TimBiegeleisen — JBull, Mar 13 '17 at 16:07
@TheOperant If that determines uniqueness, then how could the second OrderID = 1 row exist. — Hunter McMillen, Mar 13 '17 at 16:08
I think your question should've been `Finding Products that have been ordered multiple times`. — Stephen, Mar 13 '17 at 16:11
@HunterMcMillen -- an order can have any number of rows -- I'm guessing only 1 per product — Hogan, Mar 13 '17 at 16:11
Ah ok I see, it is a single item in that order. That makes sense. — Hunter McMillen, Mar 13 '17 at 16:12

Hogan · Answer 1 · 2017-03-13T16:40:42.380

0

You can use a group by and having like this

SELECT OrderID, ProductID, Quantity, Count(*) as Count
FROM Your_table_name_which_you_did_not_tell_us
GROUP BY OrderID, ProductID, Quantity
HAVING COUNT(*) > 1

edited Mar 13 '17 at 16:40

answered Mar 13 '17 at 16:13

Hogan

69,564
10
76
117

He left a comma out between ProductID and Quantity @TimBiegeleisen – JBull Mar 13 '17 at 16:22
@TheOperant -- my name is Hogan but thank you for catching the typo. – Hogan Mar 13 '17 at 16:40

score 0 · Answer 2 · answered Mar 22 '17 at 12:00

0

FYI this did the job for me;

"SELECT MIN(orderID) as orderId, COUNT(*) as TimesOrdered" + " FROM ORDERS" + " GROUP BY partId, quantity" + " HAVING COUNT(*)>1";

answered Mar 22 '17 at 12:00

JBull

43
1
8

Finding orders that have been ordered multiple times (SQL query)

2 Answers2