CODEWITHSUNDEEP
sql
user341956
user341956

Reputation: 13

SQL query to select distinct record with 2 or more repetition in another field

So I have this table of book orders, it contains 2 columns, one is the order ID(primary key) and another is the ID of the book that the customer ordered. For example:

+---------+--------+
| OrderID | BookID |
+---------+--------+
| 0001    | B002   |
| 0002    | B005   |
| 0003    | B002   |
| 0004    | B003   |
| 0005    | B005   |
| 0006    | B002   |
| 0007    | B002   |
+---------+--------+

What I want is to get the IDs of the books that got 2 or more purchases/orders, for example if I run the SQL query against the above data, I would get this as the result:

+--------+
| BookID | 
+--------+
| B002   |
| B005   |
+--------+

I don't know if this can be archived in SQL or I have to built a simpler statement and repetitive run the query against all the records in another language. I need some help and thanks for reading my question.

Upvotes: 1

Views: 5004

Answers (1)

MiKo
MiKo

Reputation: 2146

Sure it is achievable using standard SQL syntax. Use GROUP and HAVING statements:

SELECT BookId, COUNT(BookId) AS CNT
FROM YourTableName
GROUP BY BookId
HAVING COUNT(BookId) >= 2

Paste your real table name instead of YourTableName in this query.

Using SELECT BookId, COUNT(BookId) AS CNT will return:

+--------+-----+
| BookID | CNT |
+--------+-----+
| B002   | 4   |
| B005   | 2   |
+--------+-----+

Upvotes: 3

Related Questions