Two aggregation functions group by

Question

I'm trying to print the department names that have the sum of all salaries bigger than the average sum on departments.

enter image description here

SELECT d.department_name, SUM(e.salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name
HAVING SUM(e.salary) > (SELECT AVG(SUM(salary)) from employees);

In the second select, after what do I have to group by AVG(SUM(salary))?

Look at using window sets (Over partition by) this allows you to get the average inline with the select. Example: http://oracle-base.com/articles/misc/analytic-functions.php or perhaps: http://stackoverflow.com/questions/6847814/group-by-vs-partition-by-in-oracle — xQbert, Apr 27 '15 at 20:46

score 1 · Answer 1 · answered Apr 27 '15 at 22:15

You need to repeat the first query in the condition. This can be done with the WITH clause.

WITH dept_sums AS (SELECT d.department_name, SUM(e.salary) sum_salary
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name)

SELECT * FROM dept_sums d_s_1 WHERE d_s_1.sum_salary > (SELECT AVG(sum_salary) FROM dept_sums d_s_2);

score 1 · Answer 2 · answered Apr 28 '15 at 01:28

This is where window (analytic) functions come in handy. Below I am using AVG() as an analytic function to calculate the average total salary across all departments.

SELECT department_name, dept_salary FROM (
    SELECT d.department_name, SUM(e.salary) AS dept_salary
         , AVG(SUM(e.salary)) OVER ( ) AS avg_dept_salary
      FROM departments d INNER JOIN employees e
        ON d.department_id = e.department_id
     GROUP BY d.department_name
) WHERE dept_salary > avg_dept_salary;

Two aggregation functions group by

2 Answers2