Window Functions Introduction vs GROUP BY0%

The OVER() Clause & PARTITION BY Mechanics

Window Functions Introduction vs GROUP BY

Intermediate12 min read•Updated: 2026-09-12

Window Functions Introduction vs GROUP BY

Prior to modern SQL standards, aggregating data inevitably meant collapsing multiple rows into a single summary row using GROUP BY. If you wanted to view an individual employee's salary alongside their department's average salary, you had to write awkward self-joins or correlated subqueries.

Window Functions (introduced in MySQL 8.0) completely revolutionize SQL analytics. A window function performs a calculation across a set of table rows that are related to the current row, without collapsing the individual rows!

The Fundamental Contrast: GROUP BY vs Window Functions

Characteristic	`GROUP BY` Aggregation	Window Function (`OVER()`)
Row Count	Collapses rows (returns 1 row per group)	Preserves all individual rows (returns N rows)
Output Detail	Individual row details are discarded	Individual row details are retained alongside calculations
Syntax	`SELECT dept, AVG(salary) ... GROUP BY dept`	`SELECT name, salary, AVG(salary) OVER(PARTITION BY dept)`
Primary Use Case	High-level summary reports	Running totals, rankings, moving averages, row-by-row comparisons

Visualizing the Difference

Suppose we have an employees table:

emp_id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	80000
3	Carol	HR	60000
4	Dave	HR	65000

Query with GROUP BY:

1SELECT department, AVG(salary) AS avg_sal
2FROM employees
3GROUP BY department;

Result (Rows are collapsed from 4 to 2):

IT: 85000
HR: 62500

Query with Window Function:

1SELECT 
  emp_id,
  name,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) AS dept_avg_sal
7FROM employees;

Result (All 4 rows preserved!):

emp_id	name	department	salary	dept_avg_sal
1	Alice	IT	90000	85000
2	Bob	IT	80000	85000
3	Carol	HR	60000	62500
4	Dave	HR	65000	62500

Alice can immediately compare her $90,000 salary against her department's $85,000 average directly on row 1!

Basic Anatomy of a Window Function

Every window function call consists of:

1
The Function: Can be an aggregate function (SUM, AVG, COUNT, MIN, MAX) or a dedicated window function (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG).
2
The OVER() Clause: Indicates that the function operates as a window function rather than a standard aggregate.

1SELECT 
  order_id,
  order_date,
  amount,
  -- Running total across the entire company
  SUM(amount) OVER() AS grand_total,
  -- Percentage of grand total contributed by this individual order
  ROUND((amount / SUM(amount) OVER()) * 100, 2) AS pct_of_total
9FROM orders;

Execution Order in SQL

Window functions execute very late in the SQL processing pipeline:

1
FROM and JOIN
2
WHERE
3
GROUP BY and HAVING
4
Window Functions (OVER)
5
SELECT expressions
6
DISTINCT
7
ORDER BY
8
LIMIT

Crucial Rule: Because window functions execute after the WHERE clause, you cannot put a window function directly in a WHERE filter (e.g., WHERE ROW_NUMBER() OVER(...) <= 5 will fail). To filter on window function results, wrap the query in a CTE or derived table!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the primary difference between GROUP BY and Window Functions?

Question 2

Which SQL clause designates that a function is executing as a window function?

Question 3

Can you put a window function directly inside a WHERE clause (e.g., WHERE RANK() OVER(...) = 1)?

Question 4

How do you filter records based on the result of a window function?

Question 5

What does an empty OVER() clause (e.g., SUM(salary) OVER()) accomplish?

Next Lesson

The OVER() Clause & PARTITION BY Mechanics

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
CTEs vs Subqueries vs Views Comparison	The OVER() Clause & PARTITION BY Mechanics

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

The OVER() Clause & PARTITION BY Mechanics

Window Functions Introduction vs GROUP BY

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Window Functions Introduction vs GROUP BY

The Fundamental Contrast: GROUP BY vs Window Functions

Characteristic	`GROUP BY` Aggregation	Window Function (`OVER()`)
Row Count	Collapses rows (returns 1 row per group)	Preserves all individual rows (returns N rows)
Output Detail	Individual row details are discarded	Individual row details are retained alongside calculations
Syntax	`SELECT dept, AVG(salary) ... GROUP BY dept`	`SELECT name, salary, AVG(salary) OVER(PARTITION BY dept)`
Primary Use Case	High-level summary reports	Running totals, rankings, moving averages, row-by-row comparisons

Visualizing the Difference

Suppose we have an employees table:

emp_id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	80000
3	Carol	HR	60000
4	Dave	HR	65000

Query with GROUP BY:

1SELECT department, AVG(salary) AS avg_sal
2FROM employees
3GROUP BY department;

Result (Rows are collapsed from 4 to 2):

IT: 85000
HR: 62500

Query with Window Function:

1SELECT 
  emp_id,
  name,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) AS dept_avg_sal
7FROM employees;

Result (All 4 rows preserved!):

emp_id	name	department	salary	dept_avg_sal
1	Alice	IT	90000	85000
2	Bob	IT	80000	85000
3	Carol	HR	60000	62500
4	Dave	HR	65000	62500

Alice can immediately compare her $90,000 salary against her department's $85,000 average directly on row 1!

Basic Anatomy of a Window Function

Every window function call consists of:

1
The Function: Can be an aggregate function (SUM, AVG, COUNT, MIN, MAX) or a dedicated window function (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG).
2
The OVER() Clause: Indicates that the function operates as a window function rather than a standard aggregate.

1SELECT 
  order_id,
  order_date,
  amount,
  -- Running total across the entire company
  SUM(amount) OVER() AS grand_total,
  -- Percentage of grand total contributed by this individual order
  ROUND((amount / SUM(amount) OVER()) * 100, 2) AS pct_of_total
9FROM orders;

Execution Order in SQL

Window functions execute very late in the SQL processing pipeline:

1
FROM and JOIN
2
WHERE
3
GROUP BY and HAVING
4
Window Functions (OVER)
5
SELECT expressions
6
DISTINCT
7
ORDER BY
8
LIMIT

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the primary difference between GROUP BY and Window Functions?

Question 2

Which SQL clause designates that a function is executing as a window function?

Question 3

Can you put a window function directly inside a WHERE clause (e.g., WHERE RANK() OVER(...) = 1)?

Question 4

How do you filter records based on the result of a window function?

Question 5

What does an empty OVER() clause (e.g., SUM(salary) OVER()) accomplish?

Next Lesson

The OVER() Clause & PARTITION BY Mechanics

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
CTEs vs Subqueries vs Views Comparison	The OVER() Clause & PARTITION BY Mechanics

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next