英文:
ERROR in name of column created from Conditional in a query for SUM POSTGRES
问题
我试图按部门获取员工总数，但在我的查询中出现了以下错误：
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
我得到了以下错误：
错误：列"TOTAL"不存在
我该如何获取员工总数大于100？
英文:
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?
答案1
得分: 1
关于 employees 和 departamentos 表格之间的连接似乎是不必要的，因为您没有使用 employees 表格的任何列来计算员工数量。此外，表格 departamentos 存储有关相关员工数量的列有点奇怪 - 它应该按需计算，而不应存储。
话虽如此，我认为您的查询应该类似于以下内容：
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
避免在 WHERE 子句中连接表格。这不仅是一种非常古老的语法，使查询难以阅读，而且如果意外地在两个大表格之间进行交叉连接，还相当危险。请改用 JOIN。
演示：db<>fiddle
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论