ERROR in name of column created from Conditional in a query for SUM POSTGRES

Question

I am trying to get employees total by department but I have the next error in my query

SELECT d.id_departaments, SUM(d.num_employees) AS "TOTAL"
FROM employees e, departamentos d
WHERE e.id_departament = d.id_departament AND
    "TOTAL" > 100
GROUP BY
    d.id_departament
    

I got the next error:

ERROR: column "TOTAL" does not exist

How I do for getting employees total >100?

Answer 1

The join between employees and departamentos seems to be unnecessary, as you don't use any column from employees to calculate the number of employees. Also, it is a bit strange that the table departamentos has a column to store the number of related employees - it should be calculated on demand and not be stored.

That being said, your query should imho look something like this

SELECT d.id_departament, count(e.id_departament) AS TOTAL
FROM departamentos d
JOIN employees e ON e.id_departament = d.id_departament
GROUP BY d.id_departament
HAVING count(e.id_departament) > 100

Avoid joining tables in the WHERE clause. It not only a very old syntax that makes the query less readable, but also quite dangerous if you accidentally make a cross join between two large tables. Use JOIN instead.

Demo: db<>fiddle