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