When I run this command, I get the Unknown column 'Orders' in 'field list'
I am trying to get customer list with number of orders, and a new column for type. If the customer has more than 10 orders. It is a big buyer etc.
SELECT
customerTable.isActive,
(SELECT
COUNT(*)
FROM
orderTable
WHERE
orderTable.customerId = customerTable.id) AS Orders,
CASE
WHEN Orders > 10 THEN 'Big buyer'
WHEN Orders > 12 THEN 'Biggest buyer'
END AS 'Type'
FROM customerTable
Also what is the correct term when you use select in the column section of my query?
CodePudding user response:
I think this will work for you.
SELECT
customerTable.isActive,
@Orders := (SELECT COUNT(*)
FROM orderTable
WHERE orderTable.customerId = customerTable.id) AS Orders,
CASE
WHEN @Orders > 10 THEN 'Big buyer'
WHEN @Orders > 12 THEN 'Biggest buyer'
END AS 'Type'
FROM customerTable;
The SELECT
in the column list is just considered a subquery.