SQL Window Functions vs Aggregate Functions: When and How to Use Each

In SQL, both window functions and aggregate functions let you summarize and analyze data, but they work in different ways. Aggregate functions group rows together and give a single summary value per group, while window functions calculate values across a set of rows related to the current row without collapsing the results. This tutorial helps beginners understand when and how to use each.

Aggregate functions are used with the GROUP BY clause. They return one result per group of rows. Common aggregate functions include SUM(), AVG(), COUNT(), MIN(), and MAX(). For example, to find the total sales per department, you'd use:

SELECT department_id, SUM(sales) AS total_sales
FROM sales_data
GROUP BY department_id;

This query groups data by department_id and calculates the total sales for each group. Notice that the result set has one row per department.

Window functions, on the other hand, do not reduce the number of rows returned. Instead, they perform calculations across a 'window' of rows related to the current row according to your specification. They are often used with the OVER() clause.

For example, to calculate the total sales per department but still show each individual sale row, you can use:

SELECT
  department_id,
  sales,
  SUM(sales) OVER (PARTITION BY department_id) AS total_sales_per_dept
FROM sales_data;

Here, SUM(sales) OVER (PARTITION BY department_id) calculates the total sales per department but keeps all rows. This means each row shows the individual sale plus the total sales for its department.

When to use each?

Use aggregate functions when you want to reduce data by grouping rows and getting a single summary value per group.

Use window functions when you want to calculate aggregates without losing the detail of individual rows. This is helpful for running totals, ranking, moving averages, and comparing each row to a group.

Another example: Suppose you want to rank employees by their salary within each department:

SELECT
  employee_id,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

This query uses the window function RANK() to assign a rank based on salary within each department. You get the ranking without losing any employee data.

In summary, aggregate functions simplify data by grouping and summarizing. Window functions keep row details while adding your desired calculations. Both are essential tools in SQL for data analysis.