SQL: "no such column" with WITH clause-CodePudding

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)

CodePudding user response：

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).

CodePudding user response：

You can do just like Tim said. Another solution would be to just add the new table (avg_quantity) to the query, for exemple on FROM like so:

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