Why am I getting Unknown column in field list?

Question

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?

Answer 1

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.