Column announced with "as" does not exists SQL

Question

I used column "as budget", however after that when trying to use it in when section in reports that "There is no column budget".

Here is the code:

select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget

Output from console:

(psycopg2.errors.UndefinedColumn) column "required_sum" does not exist
LINE 8: WHERE budget < required_sum
                       ^

[SQL: --datediff(p.end_date, p.start_date)
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
WHERE budget < required_sum
GROUP BY ep.project_id, p.id, p.budget]
(Background on this error at: http://sqlalche.me/e/f405)

An error may have been returned because you probably used double quotes for string text. Please use single quotes for string text. Double quotes are for names of tables or fields.

It says column "required_sum" doesn't exist., not budget. And there is no such column, is there? — Cetin Basoz, Mar 21 '22 at 07:11

score 0 · Answer 1 · answered Mar 21 '22 at 07:11

use this

select * from (
select p.id,
p.budget/365 as budget,
SUM(e.salary/365) as required_sum
from linkedin_projects p
JOIN linkedin_emp_projects as ep ON ep.project_id = p.id
JOIN linkedin_employees as e ON e.id = ep.emp_id
GROUP BY ep.project_id, p.id, p.budget
)a
WHERE budget < required_sum

score 0 · Answer 2 · answered Mar 21 '22 at 07:15

The where clause cannot access information that is calculated by the group by clause, you need to use a having clause to do that:

SELECT
      p.id
    , p.budget/365 AS budget
    , SUM(e.salary/365) AS required_sum
FROM linkedin_projects p
JOIN linkedin_emp_projects AS ep ON ep.project_id = p.id
JOIN linkedin_employees AS e ON e.id = ep.emp_id
GROUP BY
      p.id
    , p.budget/365
HAVING p.budget/365 < required_sum

Column announced with "as" does not exists SQL

2 Answers2