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'

you cannot have a derived column alias name in the where clause , select clause items are defined at the end of the query execution. use "Where COUNT(DISTINCT ORDER_ID) > 3 " instead — Prashant, Aug 20 '15 at 20:56
Have posted the answer check that, even where clause will not work with grouped by values.. you have to use Having clause — Prashant, Aug 20 '15 at 21:15

score 0 · Answer 1 · answered Aug 20 '15 at 20:58

You should do it with a subquery

SELECT * FROM (
  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

score 0 · Accepted Answer · answered Aug 20 '15 at 21:14

0

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

answered Aug 20 '15 at 21:14

Prashant

460
3
12

Selecting rows based on a number of orders

2 Answers2