SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

15Jul/110

Oracle Sum Function

The Oracle Sum fuction is very useful. Also analytical Oracle Sum functions are available. Using sum(<column>) over (partition by <column>): with this oracle sum function, you can sum over a range within the result set.

Sample: I have 526 Rows of payed bills, unfortunatelly within this view I get bills with their position in the same view (not normalized). With this oracle sum function, I calculate a sum of all positions within its bill.

select persnr ,billnr ,positionnr ,betrag ,currency ,sum(betrag) over (partition by persnr, rechnungnr order by persnr, to_number(billnr),to_number(positionnr)) as oracle_sum   -- oracle sum function  from billing_view where 1=1 and persnr = 37797


Oracle Sum Result Set

PERSNRBILLNRPOSITIONNRBETRAGCURRENCYORACLE_SUM
37797
900004191
1
30
CHF
30
37797
900004191
2
-510
CHF
-480
37797
900004191
3
-100
CHF
-580
37797
900004191
4
429
CHF
-151
37797
900004191
5
84
CHF
-67
37797
900004191
6
30
CHF
-37
37797
900004191
7
-109
CHF
-146
37797
900004191
8
-20
CHF
-166
37797
900004191
9
24
CHF
-142
37797
900004191
10
4
CHF
-138
37797
900004191
11
30
CHF
-108
37797
900004191
12
-439
CHF
-547
37797
900004191
13
-81
CHF
-628
37797
900004191
14
354
CHF
-274
37797
900004191
15
65
CHF
-209
37797
900004191
16
30
CHF
-179
37797
900004191
17
-332
CHF
-511
37797
900004191
18
-74
CHF
-585
37797
900004191
19
260
CHF
-325
37797
900004191
20
58
CHF
-267
37797
900005419
1
60
CHF
60
37797
900005419
2
20
CHF
80
37797
900010226
1
30
CHF
30
37797
1000010315
1
50
CHF
50
37797
1000010315
2
100
CHF
150
37797
1000010315
3
600
CHF
750
37797
1000010315
4
480
CHF
1230
37797
1000010315
5
100
CHF
1330
37797
1000010315
6
540
CHF
1870
37797
1000010315
7
50
CHF
1920
37797
1000010315
8
60
CHF
1980
37797
1000010315
9
80
CHF
2060
37797
1000010315
10
100
CHF
2160
37797
1000010315
11
690
CHF
2850
37797
1000010315
12
50
CHF
2900
37797
1000010315
13
100
CHF
3000
37797
1000010315
14
690
CHF
3690
37797
1000010315
15
80
CHF
3770
37797
1000010315
16
60
CHF
3830
37797
1000010315
17
60
CHF
3890
37797
1000042245
1
34
CHF
34
37797
1000042245
2
-50
CHF
-16
37797
1000042245
3
34
CHF
18
37797
1000042245
4
-50
CHF
-32
37797
1000042245
5
34
CHF
2
37797
1000042245
6
-50
CHF
-48
37797
1000042245
7
34
CHF
-14
37797
1000042245
8
-50
CHF
-64
37797
1000042245
9
34
CHF
-30
37797
1000042245
10
-50
CHF
-80
37797
1000042245
11
194
CHF
114
37797
1000042245
12
-284
CHF
-170
37797
1000042245
13
34
CHF
-136
37797
1000042245
14
-50
CHF
-186
37797
1000042245
15
34
CHF
-152
37797
1000042245
16
-50
CHF
-202
37797
1000042245
17
34
CHF
-168
37797
1000042245
18
-50
CHF
-218
37797
1000042245
19
34
CHF
-184
37797
1000042245
20
-50
CHF
-234
37797
1000042245
21
34
CHF
-200
37797
1000042245
22
-50
CHF
-250
37797
1000042245
23
34
CHF
-216
37797
1000042245
24
-50
CHF
-266
37797
1000042245
25
34
CHF
-232
37797
1000042245
26
-50
CHF
-282
37797
1000042245
27
34
CHF
-248
37797
1000042245
28
-50
CHF
-298
37797
1000042245
29
34
CHF
-264
37797
1000042245
30
-50
CHF
-314
37797
1000042245
31
34
CHF
-280
37797
1000042245
32
-50
CHF
-330
37797
1000042245
33
-50
CHF
-380
37797
1000042245
34
34
CHF
-346
37797
1000042245
35
34
CHF
-312
37797
1000042245
36
-50
CHF
-362
37797
1000042245
37
34
CHF
-328
37797
1000042245
38
-50
CHF
-378
37797
1000042245
39
34
CHF
-344
37797
1000042245
40
-50
CHF
-394
37797
1000042245
41
34
CHF
-360
37797
1000042245
42
-50
CHF
-410
37797
1000042245
43
34
CHF
-376
37797
1000042245
44
-50
CHF
-426
37797
1000042245
45
34
CHF
-392
37797
1000042245
46
-50
CHF
-442
37797
1000042245
47
34
CHF
-408
37797
1000042245
48
-50
CHF
-458
37797
1000042245
49
34
CHF
-424
37797
1000042245
50
-50
CHF
-474
37797
1000042245
51
34
CHF
-440
37797
1000042245
52
-50
CHF
-490
37797
1000042245
53
34
CHF
-456
37797
1000042245
54
-50
CHF
-506
37797
1000046160
1
50
CHF
50
37797
1000046160
2
100
CHF
150
37797
1000046160
3
600
CHF
750
37797
1000046160
4
480
CHF
1230
37797
1000046160
5
100
CHF
1330
37797
1000046160
6
540
CHF
1870
37797
1000046160
7
50
CHF
1920
37797
1000046160
8
60
CHF
1980
37797
1000046160
9
80
CHF
2060
37797
1000046160
10
100
CHF
2160
37797
1000046160
11
690
CHF
2850
37797
1000046160
12
50
CHF
2900
37797
1000046160
13
100
CHF
3000
37797
1000046160
14
690
CHF
3690
37797
1000046160
15
80
CHF
3770
37797
1000046160
16
60
CHF
3830
37797
1000046160
17
60
CHF
3890
37797
1000048293
1
50
CHF
50
37797
1000053891
1
50
CHF
50
37797
1000053891
2
100
CHF
150
37797
1000053891
3
600
CHF
750
37797
1000053891
4
480
CHF
1230
37797
1000053891
5
100
CHF
1330
37797
1000053891
6
540
CHF
1870
37797
1000053891
7
50
CHF
1920
37797
1000053891
8
60
CHF
1980
37797
1000053891
9
80
CHF
2060
37797
1000053891
10
100
CHF
2160
37797
1000053891
11
690
CHF
2850
37797
1000053891
12
50
CHF
2900
37797
1000053891
13
100
CHF
3000
37797
1000053891
14
690
CHF
3690
37797
1000053891
15
80
CHF
3770
37797
1000053891
16
60
CHF
3830
37797
1000053891
17
60
CHF
3890
37797
1000054727
1
50
CHF
50
37797
1000054727
2
100
CHF
150
37797
1000054727
3
600
CHF
750
37797
1000054727
4
480
CHF
1230
37797
1000054727
5
100
CHF
1330
37797
1000054727
6
540
CHF
1870
37797
1000054727
7
50
CHF
1920
37797
1000054727
8
60
CHF
1980
37797
1000054727
9
80
CHF
2060
37797
1000054727
10
100
CHF
2160
37797
1000054727
11
690
CHF
2850
37797
1000054727
12
50
CHF
2900
37797
1000054727
13
100
CHF
3000
37797
1000054727
14
690
CHF
3690
37797
1000054727
15
80
CHF
3770
37797
1000054727
16
60
CHF
3830
37797
1000054727
17
60
CHF
3890
37797
1000054727
18
50
CHF
3940
37797
1000054727
19
419
CHF
4359
37797
1000054727
20
78
CHF
4437
37797
1000054882
1
-60
CHF
-60
37797
1000054882
2
-60
CHF
-120
37797
1000054882
3
14
CHF
-106
37797
1000054882
4
14
CHF
-92
37797
1000054882
5
38
CHF
-54
37797
1000054882
6
-50
CHF
-104
37797
1000054882
7
46
CHF
-58
37797
1000054882
8
11
CHF
-47
37797
1000054882
9
50
CHF
3
37797
1000054882
10
-38
CHF
-35
37797
1000054882
11
59
CHF
24
37797
1000054882
12
50
CHF
74
37797
1000054882
13
-50
CHF
24
37797
1000054882
14
11
CHF
35
37797
1000055210
1
77
CHF
77
37797
1000055210
2
394
CHF
471
37797
1000058039
1
417
CHF
417
37797
1000058039
2
73
CHF
490
37797
1000058039
3
73
CHF
563
37797
1000058039
4
73
CHF
636
37797
1000058039
5
73
CHF
709
37797
1000058039
6
73
CHF
782
37797
1000058039
7
73
CHF
855
37797
1000058039
8
73
CHF
928
37797
1000058039
9
73
CHF
1001
37797
1000058039
10
73
CHF
1074
37797
1000058039
11
73
CHF
1147
37797
1000058039
12
73
CHF
1220
37797
1000058039
13
73
CHF
1293
37797
1000058039
14
73
CHF
1366
37797
1000058039
15
73
CHF
1439
37797
1000058039
16
73
CHF
1512
37797
1000058039
17
73
CHF
1585
37797
1000058039
18
73
CHF
1658
37797
1000058039
19
73
CHF
1731
37797
1000058039
20
73
CHF
1804
37797
1000058039
21
73
CHF
1877
37797
1000058039
22
73
CHF
1950
37797
1000058039
23
73
CHF
2023
37797
1000058039
24
73
CHF
2096
37797
1000058039
25
73
CHF
2169
37797
1000058039
26
73
CHF
2242
37797
1000058039
27
73
CHF
2315
37797
1000062802
1
60
CHF
60
37797
1000064602
1
240
CHF
240
37797
1000064602
2
-240
CHF
0
37797
1000064602
3
300
CHF
300
37797
1000064602
4
67
CHF
367
37797
1000064602
5
50
CHF
417
37797
1000065941
1
73
CHF
73
37797
1000066157
1
77
CHF
77
37797
1000066157
2
394
CHF
471
37797
1000066685
1
25
CHF
25
37797
1000069908
1
20
CHF
20
37797
1000070001
1
417
CHF
417
37797
1000070001
2
73
CHF
490
37797
1000070001
3
73
CHF
563
37797
1000070001
4
73
CHF
636
37797
1000070001
5
73
CHF
709
37797
1000070001
6
73
CHF
782
37797
1000070001
7
73
CHF
855
37797
1000070001
8
73
CHF
928
37797
1000070001
9
73
CHF
1001
37797
1000070001
10
73
CHF
1074
37797
1000070001
11
73
CHF
1147
37797
1000070001
12
73
CHF
1220
37797
1000070001
13
73
CHF
1293
37797
1000070001
14
73
CHF
1366
37797
1000070001
15
73
CHF
1439
37797
1000070001
16
73
CHF
1512
37797
1000070001
17
73
CHF
1585
37797
1000070001
18
73
CHF
1658
37797
1000070001
19
73
CHF
1731
37797
1000070001
20
73
CHF
1804
37797
1000070001
21
73
CHF
1877
37797
1000070001
22
73
CHF
1950
37797
1000070001
23
73
CHF
2023
37797
1000070001
24
73
CHF
2096
37797
1000070001
25
73
CHF
2169
37797
1000070001
26
73
CHF
2242
37797
1000070001
27
73
CHF
2315

Related Posts:

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.