Question 22102 in Mysql Question posted in
The official MySQL documentation can be found here.

Divide one table by another table created in the same query – Mysql

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search