CODEWITHSUNDEEP
sqloracle-database
Shury
Shury

Reputation: 578

Two aggregation functions group by

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))?

Upvotes: 0

Views: 80

Answers (2)

David Faber
David Faber

Reputation: 12495

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;

Upvotes: 1

fredt
fredt

Reputation: 24372

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);

Upvotes: 1

Related Questions