Window Functions vs Aggregate Functions in SQL: A Beginner-Friendly Guide
If you have written SQL for more than a week, you have used SUM() , AVG() or COUNT() . They are the bread and butter of reporting. Then one day someone says, "Just use a window function for that" , and suddenly you are staring at a query with OVER (PARTITION BY ...) in it and wondering what you missed. This article is for you if you are just starting with window functions , or if you already know…
Window functions and aggregate functions are two types of SQL operations that perform calculations on data. Aggregate functions like SUM, AVG, and COUNT reduce multiple rows into a single value, while window functions calculate values across multiple rows but keep each row intact.
Consider calculating total sales per product. Using an aggregate function, you get two rows: one for Caps with a total of 40, and another for Gloves with a total of 25. This is achieved by grouping the rows by product and summing the sales for each group.
However, when using window functions, you retain all four original rows. Each row now displays the product total alongside its individual sales. For example, the Caps row with a sales value of 10 shows the total sales for Caps as 40, while the Gloves rows display the same total of 25. This preserves the granularity of the data, allowing you to see both individual sales and the group total.
Window functions can be particularly useful when you need to compare each row to its group's average or maximum value. For instance, to find how far each employee's salary is from their department's average salary, you can use a window function to calculate the department's average salary and then subtract it from each employee's salary. This preserves the individual rows, allowing for more detailed analysis without losing the original data.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
