Selecting rows based on a number of orders

Question

My table PURCHASES has the following columns: NAME, ORDER_ID, ITEM_ID.

When a client orders 2 items, it is recorded with 2 rows under same ORDER_ID and two identical or different ITEM_ID (depending if the customer has bought the same items or not).

I would like to select all the clients who made a total of less than 3 orders and less than 4 items (regardless if they are identical). Here is the current code:

SELECT NAME, COUNT(DISTINCT ORDER_ID) AS number_of_orders, COUNT(ITEM_ID) AS number_of_items
FROM `PURCHASES`
WHERE number_of_orders <3
AND number_of_items <4
GROUP BY NAME
ORDER BY number_of_items DESC

I get the following error message: Unknown column 'number_of_orders' in 'where clause'

Answer 1

Use this query

SELECT NAME, COUNT(DISTINCT ORDER_ID) AS number_of_orders, COUNT(ITEM_ID) AS
number_of_items
FROM `PURCHASES`
GROUP BY NAME
HAving COUNT(DISTINCT ORDER_ID) <3
AND COUNT(ITEM_ID) <4
ORDER BY number_of_items DESC