Divide one table by another table created in the same query

November 23, 2022

I have the following SQL query:

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END) as Advisories,
FROM parks
GROUP BY `NeighbourhoodName`;

In the second line of the code, I create a column called "Number of Parks". I would like all the values in the next column (Advisories) to be divided by the values in "Number of parks". However, when I try to insert the division statement after the column like this:

SELECT `NeighbourhoodName`,
count(NAME) as `Number of Parks`,
sum(CASE 
    WHEN `parks`.`Advisories` = 'Y' THEN 1
    ELSE 0 
END)/`Number of Parks` as Advisories
FROM parks
GROUP BY `NeighbourhoodName`;

I get the following error:

Unknown column, `Number of Parks` in field list.

How can I perform this division while still keeping it in one query?

>Solution :

You need to use an outer query to do the division.

SELECT `NeighbourhoodName`,
       `Number of Parks`,
        Advisories/`Number of Parks` as Advisories
FROM    ( SELECT `NeighbourhoodName`,
                 count(NAME) as `Number of Parks`,
                 sum( CASE  WHEN `parks`.`Advisories` = 'Y' THEN 1 ELSE 0 END ) as Advisories
          FROM parks
          GROUP BY `NeighbourhoodName`
         ) as tbl;

Problems with Column Aliases

An alias can be used in a query select list to give a column a
different name. You can use the alias in GROUP BY, ORDER BY, or HAVING
clauses to refer to the column.