Divide one table by another table created in the same query-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Or use the expression of count(Name) instead of Number of Parks:

select NeighbourhoodName,
       count(Name)      as `Number of Parks`,
       sum(case when Advisories='Y' then 1 else 0 end)
       /count(Name)     as Advisories
  from parks
 group by NeighbourhoodName;