OVER Clause

In my free time, I found interesting query that remind me about stored procedure in my company. That stored procedure used “self join” clause to combine raw table with itself but processed by aggregate function (SUM, AVG, etc.). But, when I read some ebook, I found that OVER Clause can handle problem above with a simple way.

This is an example data:

Name

Amount

Arif 3
Arif 2
Arif 1
Nasution 3
Nasution 2

 

The expected result :

Name

Amount

Sum

Arif 3 6
Arif 2 6
Arif 1 6
Nasution 3 5
Nasution 2 5

 

Example query :
[code language=”css”]
SELECT nama, jumlah, SUM(jumlah) OVER (PARTITION BY nama), AVG(jumlah) OVER (PARTITION BY nama) FROM
(
SELECT ‘arif’ AS nama, 3 AS jumlah UNION ALL
SELECT ‘arif’ AS nama, 2 AS jumlah UNION ALL
SELECT ‘arif’ AS nama, 1 AS jumlah UNION ALL
SELECT ‘nasution’ AS nama, 3 AS jumlah UNION ALL
SELECT ‘nasution’ AS nama, 2 AS jumlah
) c
[/code]
 

For performance aspect, I still search best clause :).

I hope this help.

Marifnst, 2013-07-31

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro