Aggregate function & Windows functions

To calculate the sum of salaries based on department ID using both aggregate and window functions in SQL, you can follow these steps. I'll provide examples for both approaches, along with explanations.

1. Using Aggregate Functions

Aggregate functions are used to perform a calculation on a set of values and return a single value. In this case, you can use the SUM() function to get the total salary for each department. Here’s a query that demonstrates this:

SELECT
department_id,
SUM(salary) AS total_salary
FROM
employees
GROUP BY
department_id;

Explanation:

SELECT department_id: This selects the department ID from the employees table.

SUM(salary) AS total_salary: This calculates the sum of the salary column for each group of department_id and aliases the result as total_salary.

FROM employees: This indicates the table from which to retrieve the data.

GROUP BY department_id: This groups the result set by department_id, allowing the SUM() function to compute the total salary for each department.

1. Using Window Functions

Window functions perform calculations across a set of rows that are related to the current row. Unlike aggregate functions, they do not reduce the number of rows returned. Here’s how you can calculate the total salary per department using a window function:

SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM
employees;

Explanation:

SELECT employee_id, department_id, salary: This selects the employee_id, department_id, and salary columns from the employees table.

SUM(salary) OVER (PARTITION BY department_id) AS total_salary: This calculates the sum of salary for each department. The PARTITION BY department_id clause defines the groups (or partitions) of rows within which the SUM() function is applied. Each row in the result set will show the total salary for its respective department.

FROM employees: This indicates the table from which to retrieve the data.

Differences Between Aggregate and Window Functions

Aggregate Functions:

Reduce the number of rows returned (one row per group).

Use GROUP BY to specify how to group the rows.

Window Functions:

Do not reduce the number of rows returned; all rows are retained.

Use OVER with PARTITION BY to define how the calculation should be applied within each partition.

Example Data

Let's say your employees table looks like this:

Result of Aggregate Function Query

The aggregate function query will yield:

Result of Window Function Query

The window function query will yield:

Both approaches are useful depending on whether you want to summarize data or keep all rows while providing the total salary per department.