Window Functions vs. Aggregate Functions in SQL

INTRODUCTION
SQL provides several ways to analyze data, and two important concepts are aggregate functions and window functions. Although both can perform calculations such as SUM(), AVG(), and COUNT(), they work differently and are useful for different analytical tasks.
1. Aggregate Functions

Aggregate functions calculate a value from multiple rows and return one result for each group of rows. Common aggregate functions include:

SUM() – calculates a total
AVG() – calculates an average
COUNT() – counts rows
MIN() – finds the minimum value
MAX() – finds the maximum value

Example

Suppose we have a sales table:

employee	department	sales
John	Sales	5000
Mary	Sales	7000
Peter	IT	4000
Jane	IT	6000

To calculate total sales for each department:

``
SELECT department, SUM(sales) AS total_sales
FROM sales
GROUP BY department;
``

Result:
| department | total_sales |
| ---------- | ----------: |
| Sales | 12000 |
| IT | 10000 |

Notice that the original individual rows are collapsed into groups.

2. Window Functions

A window function performs a calculation across a set of related rows while keeping the individual rows in the result.

Window functions use the OVER() clause.

Example

`SELECT 
    employee,
    department,
    sales,
    SUM(sales) OVER(PARTITION BY department) AS department_total
FROM sales;`

Result:
| employee | department | sales | department_total |
| -------- | ---------- | ----: | ---------------: |
| John | Sales | 5000 | 12000 |
| Mary | Sales | 7000 | 12000 |
| Peter | IT | 4000 | 10000 |
| Jane | IT | 6000 | 10000 |

Here, the department total is calculated, but each employee's original row remains visible

3. Key Difference

The main difference is how the functions treat rows:
| Feature | Aggregate Functions | Window Functions |
| ------------------------ | -------------------- | ------------------------------------- |
| Main purpose | Summarize data | Analyze data while retaining rows |
| Common syntax | SUM(sales) | SUM(sales) OVER(...) |
| Uses GROUP BY | Usually | Not required |
| Individual rows retained | | |
| Useful for | Totals and summaries | Rankings, running totals, comparisons |

4. Ranking Example

Window functions are particularly useful for ranking.

`SELECT 
    employee,
    sales,
    RANK() OVER(ORDER BY sales DESC) AS sales_rank
FROM sales;`

This produces a ranking for each employee based on their sales without removing any employee from the result.

5. Running Total Example

Window functions can also calculate a running total:

`SELECT 
    employee,
    sales,
    SUM(sales) OVER(ORDER BY employee) AS running_total
FROM sales;`

This is useful in financial analysis, sales reporting, and business dashboards.

Conclusion

Aggregate functions are mainly used to reduce multiple rows into summary results, often with GROUP BY. Window functions, on the other hand, perform calculations across related rows while preserving the original rows.