PostgreSQL Aggregate Functions and Group By: Get the department ID and the total salary payable in each department

10. Write a query to get the department ID and the total salary payable in each department.

Sample Solution:

Code:

-- This SQL query retrieves the total salary for each department.

SELECT department_id, SUM(salary) -- Selects the department_id and calculates the sum of salaries for each department
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
GROUP BY department_id; -- Groups the results by department_id, so that the sum of salaries is calculated for each unique department_id

Explanation:

• The SQL query calculates the total salary for each department.
• The SELECT statement selects both the department_id and the sum of salary for each department.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The GROUP BY clause groups the results by department_id, ensuring that the sum of salaries is calculated for each unique department.
• For each department, the SUM() function calculates the total salary by adding up all the salaries of employees within that department.
• The result set will contain one row for each unique department_id, along with the total salary for each department.

Sample table: employees

Output:

pg_exercises=# SELECT department_id, SUM(salary)
pg_exercises-# FROM employees
pg_exercises-# GROUP BY department_id;

 department_id |    sum
---------------+-----------
            90 |  58090.00
            20 |  19060.00
           100 |  51780.00
            40 |   6530.00
           110 |  20360.00
            80 | 295990.00
            70 |  10030.00
            50 | 157750.00
            60 |  28950.00
            30 |  25080.00
            10 |   4430.00
             0 |   7030.00
(12 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to find the manager ID and the salary of the lowest-paid employee under that manager.
Next: Write a query to get the average salary for each post excluding programmer.