Select from the same table with two columns-CodePudding

I have a table ("MyOrders") with two columns ("user_id" and "order_id")

What could be the shortest SELECT command to return the list of all users who have 10 or more orders?

I tried this

SELECT MyOrders.user_id, MyOrders.order_id 
FROM MyOrders 
WHERE orders >= 10;

CodePudding user response：

You can use GROUP BY and HAVING

SELECT MyOrders.user_id, count(MyOrders.order_id) as cnt
FROM MyOrders
GROUP BY  MyOrders.user_id
HAVING cnt>=10;

CodePudding user response：

lucumt is correct.

Just to make it a bit better, you can use this.

SELECT MyOrders.user_id as `User ID`, count(MyOrders.order_id) as `Orders 
Count`
FROM MyOrders
GROUP BY  MyOrders.user_id
HAVING `Orders Count` >= 10
ORDER BY `Orders Count` DESC;

This query would not count the orders if order_id is empty or null. If you want to count them though even when the order_id is empty/null, you can just count user_id instead of order_id.

SELECT MyOrders.user_id as `User ID`, count(MyOrders.user_id) as `Orders Count`
FROM MyOrders
GROUP BY  MyOrders.user_id
HAVING `Orders Count` >= 10
ORDER BY `Orders Count` DESC;