MySQL Window Functions: OVER() and PARTITION BY

Window topic	Purpose
OVER() / PARTITION BY	Aggregate values while preserving each row.
RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()	Rank and number rows within a window.
LEAD() / LAG()	Compare current rows with following or previous rows.

GROUP BY collapses rows into one result row per group. A window function uses OVER() to calculate across a set of rows while keeping each original result row visible.

The term window is used here for a group of rows or a set of records, it is NOT related to Microsoft Windows.

We may want to list all rows and at the same time display grouped result. Say along with individual rows with marks the sum of grouped (aggregate ) mark of the full result can be displayed.
Check this SQL.

SELECT class, SUM(mark) AS total
FROM student
WHERE class = 'Three'
GROUP BY class;

Output is one row for the selected class.

class	total
Three	221

Window functions are available in MySQL 8.0 and later.

Using OVER()

SELECT id, name, class, mark, gender,
       SUM(mark) OVER () AS total
FROM student
WHERE class = 'Three';

Output is here ( watch the total column which is single global sum for all rows taken as a group and shown against each row of result )

id	name	class	mark	gender	total
2	Max Ruin	Three	85	male	221
3	Arnold	Three	55	male	221
27	Big Nose	Three	81	female	221

Aggregate functions such as AVG(), MAX(), MIN(), SUM() and COUNT() can also operate as window functions when an OVER() clause is present.

Here we are trying to display each student mark and compare it with aggregate over another column.

SELECT id, name, class, mark, gender,
       SUM(mark) OVER () AS total,
       AVG(mark) OVER () AS average_mark,
       MAX(mark) OVER () AS highest_mark,
       MIN(mark) OVER () AS lowest_mark
FROM student
WHERE class = 'Three';

Output

id	name	class	mark	gender	total	avg	max	min
2	Max Ruin	Three	85	male	221	73.667	85	55
3	Arnold	Three	55	male	221	73.667	85	55
27	Big Nose	Three	81	female	221	73.667	85	55

Using PARTITION BY

With an empty OVER(), all selected rows form one window. PARTITION BY divides them into smaller windows without collapsing the rows.

SELECT name, class, mark,
       SUM(mark) OVER () AS total,
       SUM(mark) OVER (PARTITION BY class) AS class_total
FROM student
WHERE id < 10;

Output : The first OVER() ( total) gives us sum of the total collection of result, the second OVER() ( class_total ) gives us sum by grouping the result across the class.

id	name	class	mark	gender	total	class_total
7	My John Rob	Five	78	male	631	163
8	Asruid	Five	85	male	631	163
1	John Deo	Four	75	female	631	250
4	Krish Star	Four	60	female	631	250
5	John Mike	Four	60	female	631	250
6	Alex John	Four	55	male	631	250
9	Tes Qry	Six	78	male	631	78
2	Max Ruin	Three	85	male	631	140
3	Arnold	Three	55	male	631	140

We can further group result in more than one column.

SELECT id, name, class, mark, gender,
       SUM(mark) OVER () AS total,
       SUM(mark) OVER (PARTITION BY class, gender) AS class_gender_total
FROM student
WHERE id < 20;

To sort the final result without changing the window calculation, use an outer ORDER BY. Putting ORDER BY inside an aggregate window definition can change the window frame and therefore the calculated value.

SELECT id, name, class, mark, gender,
       SUM(mark) OVER () AS total,
       SUM(mark) OVER (PARTITION BY class, gender) AS class_gender_total
FROM student
WHERE id < 20
ORDER BY class, gender, mark;

Aggregate windows functions

AVG() BIT_AND() BIT_OR() BIT_XOR() COUNT() JSON_ARRAYAGG() JSON_OBJECTAGG() MAX() MIN() STDDEV_POP(), STDDEV(), STD() STDDEV_SAMP() SUM() VAR_POP(), VARIANCE() VAR_SAMP()

nonaggregate functions

CUME_DIST() FIRST_VALUE() LAST_VALUE() LAG(),LEAD()
NTH_VALUE() PERCENT_RANK()
RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()

SQL Math References Sum query Sum Multiple column

GROUP BY Ranking Functions LEAD() / LAG() Multiple-column Totals

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Window Functions with OVER()

Using OVER()

Using PARTITION BY

Aggregate windows functions

nonaggregate functions

Subhendu Mohapatra