SQL not finding a column and cant use a condition to filter data [duplicate]

Question

i'm currently a student and while doing an SQL excercise i had a question and i tried to do a thing. So this is the main query

SELECT
    Date,
    Region,
    Total_Bags,
    Small_Bags,
       (Small_Bags / Total_Bags) *100 AS Small_BagsPercent
FROM sql-data-analys-corso.avocado_dataset.avocado_prices
WHERE Total_Bags <>0
ORDER BY Small_BagsPercent

I wanted SQL to show me only Small_BagsPercent > 10 but the query tells me that Unrecognized name: Small_BagsPercent at [9:5] if i write the query like this

SELECT
    Date,
    Region,
    Total_Bags,
    Small_Bags,
   (Small_Bags / Total_Bags) *100 AS Small_BagsPercent
FROM sql-data-analys-corso.avocado_dataset.avocado_prices
WHERE Total_Bags <>0
AND Small_BagsPercent >10
ORDER BY Small_BagsPercent

and i dont understand why, do i have to use a subquery? How can i do that?

Answer 1

You can't use column aliases in the WHERE clause because the WHERE clause is processed before the SELECT clause. You can write the calculation in WHERE clause

SELECT
    Date,
    Region,
    Total_Bags,
    Small_Bags,
   (Small_Bags / Total_Bags) *100 AS Small_BagsPercent
FROM sql-data-analys-corso.avocado_dataset.avocado_prices
WHERE Total_Bags <>0
AND (Small_Bags / Total_Bags) *100 > 10
ORDER BY Small_BagsPercent

One more option is writing Sub Query

SELECT *
FROM (
    SELECT
        Date,
        Region,
        Total_Bags,
        Small_Bags,
        (Small_Bags / Total_Bags) * 100 AS Small_BagsPercent
    FROM sql-data-analys-corso.avocado_dataset.avocado_prices
    WHERE Total_Bags <> 0
) AS AvocadoData
WHERE Small_BagsPercent > 10
ORDER BY Small_BagsPercent

Answer 2

The WHERE clause takes place before the SELECT clause. So at this point in time, Small_BagsPercent is something unknown to the DBMS.

You can use a subquery to go about this:

SELECT *
FROM
(
  SELECT
    date,
    region,
    total_bags,
    small_bags,
    (small_bags / total_bags) * 100 AS small_bagspercent
  FROM sql-data-analys-corso.avocado_dataset.avocado_prices
  WHERE Total_Bags <> 0
) calculated
WHERE small_bagspercent > 10
ORDER BY small_bagspercent;