MySQL over() with partition to add aggregate functions to get windows against each row

When we use aggregate function with group by query, we get single row output because the query Group by groups result rows into single result row.

By using window function over() with partition we can group result and associates the same with each result row.

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 id,name,class,mark,gender,sum(mark) as total
  from student WHERE class='Three' group by class

Output is grouped into a single result row.

id	name	class	mark	gender	total
2	Max Ruin	Three	85	male	221

Note : over() , Partition are available in MySQL – 8 and above only.

using over()

SELECT id, name,class,mark,gender,sum(mark) over() 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

While displaying the mark of each student we can use the aggregate function like avg() , max(), min() , sum() , count() etc.

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() total,
avg(mark) over() avg,
max(mark) over() max,
min(mark) over() min 
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

We have not used any parameter inside over(), now we will specify how the partition query rows into groups for processing by window function. We expanded our selection of records by modifying WHERE condition to collect 10 records including different class.

SELECT  name,class,mark,
sum(mark) over() total,
sum(mark) over(PARTITION BY class) 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() total,
sum(mark) over(PARTITION BY class,gender) class_total
FROM student where id<20;

We can use ORDER BY in our Query to display results in the order of classes.

SELECT id, name,class,mark,gender,
sum(mark) over() total,
sum(mark) over(PARTITION BY class,gender ORDER BY class ) class_total
FROM student where id<20;

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

Subscribe to our YouTube Channel here

over() with Partition using MySQL table

using over()

Using PARTITION

Aggregate windows functions

nonaggregate functions

Subscribe