SQL: "no such column" with WITH clause

February 2, 2023

I’m learning SQL and I would like to know why I’m getting ‘Error 1: could not prepare statement (1 no such column: avg_quantity)’ when I try the following query on the
w3schools

WITH avg_quantity AS (SELECT AVG(OrderDetails.Quantity) 
                      FROM OrderDetails)
SELECT Products.ProductName, OrderDetails.Quantity
FROM OrderDetails
JOIN Products ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.Quantity > avg_quantity

But when I try this code here, everything works fine:

SELECT Products.ProductName, OrderDetails.Quantity
FROM OrderDetails
JOIN Products ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.Quantity > (SELECT AVG(OrderDetails.Quantity) 
                               FROM OrderDetails)

>Solution :

You still need a select against the CTE:

WITH cte AS (
    SELECT AVG(Quantity) AS avg_quantity
    FROM OrderDetails
)

SELECT p.ProductName, od.Quantity
FROM OrderDetails od
INNER JOIN Products p ON p.ProductID = od.ProductID
WHERE od.Quantity > (SELECT avg_quantity FROM cte);

Functionally speaking, a CTE behaves like inlined SQL code. It is not something like a user defined variable which stores some value (although on some RDBMS, CTEs can be materialized).