sql - 在 PostgreSQL 查询中使用别名返回 "column does not exist"

Question

我正在尝试使用来自其他选择查询的别名在 PostgreSQL 中获取计算列的结果。我想将 totalScore 除以 totalEmployee 以获得该部分的平均值。但是当我尝试执行计算时出现错误。

SELECT
    cast( sum( empskills.score ) as dec( 2 , 1 ) ) AS totalScore,
    skills.skill_name,  
    (SELECT
        count(employees.first_name)
        FROM
        sections
        INNER JOIN employees
        ON sections.id = employees.section_id
        WHERE
        sections.id = 2) as totalEmployees,
    (SELECT totalScore / totalEmployees) as finalResult
FROM
    employees
    INNER JOIN empskills
     ON employees.id = empskills.empid
    INNER JOIN skills
     ON empskills.skillid = skills.id
    INNER JOIN sections
     ON employees.section_id = sections.id
WHERE
    sections.id = 2
GROUP BY
    skills.skill_name

ERROR:  column "totalscore" does not exist
LINE 17:  (SELECT totalScore / totalEmployees)

我期望得到除法方程的结果。

Answer 1

根据你上面的SQL，我想你只是想得到每个员工每项技能的平均分，我试着根据你上面的SQL写了一个更简洁的SQL如下:

select
    s.skill_name,
    sum(k.score) as totalScore,
    count(distinct e.id) as totalEmployees,
    sum(k.score*1.0)/count(distinct e.id) as finalResult
from
    employees e
join
    empskills k on e.id = k.empid
join
    skills s on k.skillid = s.id
where
    e.section_id = 2
group by
    s.skill_name