over() with Partition using MySQL table

over() with Partition 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. 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.
idnameclassmarkgendertotal
2Max RuinThree85male221

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 )
idnameclassmarkgendertotal
2Max RuinThree85male221
3ArnoldThree55male221
27Big NoseThree81female221
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
idnameclassmarkgendertotalavgmaxmin
2Max RuinThree85male22173.6678555
3ArnoldThree55male22173.6678555
27Big NoseThree81female22173.6678555

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.
idnameclassmarkgendertotalclass_total
7My John RobFive78male631163
8AsruidFive85male631163
1John DeoFour75female631250
4Krish StarFour60female631250
5John MikeFour60female631250
6Alex JohnFour55male631250
9Tes QrySix78male63178
2Max RuinThree85male631140
3ArnoldThree55male631140
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

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer