每个部门的甲骨文平均工资

Question

我有以下代码，该代码找到了每个部门的平均工资，并且效果很好。

我在输出中添加dections_name时遇到了问题。我知道我必须加入桌子，但我无法使它工作。

任何帮助将不胜感激。感谢所有回答的人。

CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id, sal,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 100000, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1, 50000,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chang',1, 100000, 'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Dunbar',1, 75000, 'A1425' FROM DUAL UNION ALL
SELECT 5, NULL, 'Emily', 'Eden',2, 90000, 'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Finn',1, 88500,'C1726' FROM DUAL UNION ALL
SELECT 7,5, 'Grace', 'Gelfenbein',2, 55000, 'C1727' FROM DUAL;
 
SELECT
    department_id,
    ROUND(AVG(sal), 2) AS AVERAGE_SALARY
FROM employees 
group by 
department_id;

Expected output

DEPARTMENT_ID DEPARTMENT_NAME AVERAGE_SALARY
1    IT         82700
2    SALES 72500

Answer 1

由于每个组中只有一个 dections_name ，因此您可以加入表并在 dectment_name 上使用聚合函数：

SELECT e.department_id,
       MAX(d.department_name) AS department_name,
       ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM   employees e
       INNER JOIN dept d
       ON (e.department_id = d.department_id)
GROUP BY e.department_id;

或者，您可以汇总然后加入

SELECT e.department_id,
       d.department_name,
       e.average_salary
FROM   (
         SELECT department_id,
                ROUND(AVG(sal), 2) AS average_salary
         FROM   employees
         GROUP BY department_id
       ) e
       INNER JOIN dept d
       ON (e.department_id = d.department_id);

：您的样本数据，两者都输出：

dectiber_id	dectiber_name	平均_salary
2	sales	72500
1	it	82700

db＆lt; /a >

Answer 2

您可以使用这样的查询：

SELECT
    d.department_id,
    d.department_name,
    ROUND(AVG(e.sal), 2) AS AVERAGE_SALARY
FROM employees e inner join dept d on e.department_id = d.department_id
group by 
d.department_id, d.department_name;

以下是一个示例：

基于Department_ID的部门加入员工。

当您组合时，包括统计信息中未包含的两个字段。这将为您提供有关您要寻找的两个字段的统计信息。

您的结果将是

 dections_id | Deppys_name |平均_salary
--------------：| ：----------------- | -------------------：
            2 |销售| 72500
            1 |它| 82700