SQL query can't use variable in FROM statement

Question

I'm new to SQL, so sorry for maybe stupid question.

Table will be from this SQL sandbox: https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

There is table of format

OrderDetailID   OrderID ProductID   Quantity
1               10248   11          12
2               10248   42          10
3               10248   72          5
4               10249   14          9
5               10249   51          40

I want to get products with maximum average quantity.

I can get this using the following query:

SELECT avg.ProductID, avg.Quantity
FROM (
  SELECT ProductID, AVG(Quantity) Quantity
  FROM OrderDetails
  GROUP BY ProductID
) avg
WHERE avg.Quantity = (
    SELECT MAX(Quantity) FROM (
      SELECT ProductID, AVG(Quantity) Quantity
      FROM OrderDetails
      GROUP BY ProductID
    )
)

ProductID   Quantity
8           70
48          70

Here I twice use block

  SELECT ProductID, AVG(Quantity) Quantity
  FROM OrderDetails
  GROUP BY ProductID

because if I use query with avg instead of second block

SELECT avg.ProductID, avg.Quantity
FROM (
  SELECT ProductID, AVG(Quantity) Quantity
  FROM OrderDetails
  GROUP BY ProductID
) avg
WHERE avg.Quantity = (SELECT MAX(Quantity) FROM avg)

I get error could not prepare statement (1 no such table: avg)

So my question is:

Is it a kind of syntaxis mistake and could be simply corrected, or for some reason I can't use variables like that?
Is there simplier way to make the query I need?

From w3schools.com/sql/trysql.asp?filename=trysql_asc : Our Try-SQL Editor uses WebSQL to demonstrate SQL. — Mikhail Kuzin
– Mikhail Kuzin, Commented Dec 7, 2018 at 21:33

Parfait · Accepted Answer · 2018-12-07 21:47:16Z

4

Consider Common Table Expressions (CTE) using WITH clause which allows you to avoid repeating and re-calculating the aggregate subquery. Most RDBMS's supports CTEs (fully valid in your SQL TryIt linked page).

WITH avg AS (
  SELECT ProductID, AVG(Quantity) Quantity
  FROM OrderDetails
  GROUP BY ProductID
)

SELECT avg.ProductID, avg.Quantity
FROM avg
WHERE avg.Quantity = (
    SELECT MAX(Quantity) FROM avg
)

answered Dec 7, 2018 at 21:47

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dávid Laczkó · Accepted Answer · 2018-12-07 22:29:51Z

0

This is not really a syntax thing, this is rather scope: you try to reference an alias where it is not in a parent-child relationship. Only this way they can reference each other. (The identifier there is an alias not a variable - that's a different thing.)
A simpler way is to create a temporary set before you run the filter condition - as in a previous answer, with a CTE, or you can try with a temp table. These can be used anywhere because their scope is not within a subquery.

edited Dec 7, 2018 at 22:29

answered Dec 7, 2018 at 22:22

Dávid Laczkó

1,0662 gold badges7 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL query can't use variable in FROM statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related