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;