Analytic Function SUM returns Average for window with same values

Question

I was trying to write a query that resulted the running sum of given values. However, when applied SUM as analytic function I got result with average within a window.

Example: Consider the following query:

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name) running_sum
from tbl;

Result:

What I actually wanted was

Which I got using ROWNUM:

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by rownum) running_sum
from tbl;

The running_sum for peter that is being displayed in the first result is actually the average of the total running_sum for peter. The analytic function is considering a window for peter as I've included "NAME" in windowing clause. But why is the query resulting in average for the window instead of running sum?

Why do you think those are averages, and of what? They are the sums...? Ordering by name, or at least just by name, in doesn't really make sense - as all three rows for peter order the same. Is there some other field you haven't shown, like a timestamp, that decides the order they should be evaluated? — Alex Poole, Dec 14 '18 at 12:42
I can only guess that there is an implicit GROUP BY involved since you're using an aggregate function, first by name and in the second case by rownum — Joakim Danielson, Dec 14 '18 at 12:42

Alex Poole · Accepted Answer · 2018-12-14T13:08:42.783

why is the query resulting in average for the window instead of running sum?

It isn't an average for the window. In your rownum version the four values it shows are 6270, 6540, 6630 and 7080 - which average out to 6630.

It is the running sum, but perhaps not of quite what you expected, and the order the output is being shown is obscuring the logic you're actually applying a bit.

You can see where the numbers you are seeing are coming from by ordering the output:

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name) running_sum
from tbl
order by name;

NAME         VAL RUNNING_SUM
----- ---------- -----------
hary        2772        2772
john        4000        6772
may       2227.5      8999.5
peter        450     10079.5
peter        270     10079.5
peter        270     10079.5
peter         90     10079.5
sia        20000     30079.5
steve       2000     32079.5
tom          500     32579.5

You can see that the running totals now make sense, from the order they are being evaluated by your windowing clause. All four values for peter are included in the running total for each of those lines - because that's all that's in the order by - and that total of 450+270+270+90=1080 is being added to the previous name's total of 8999.5.

You could get different values for each peter row by including a row-based window clause:

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name
          rows between unbounded preceding and current row) running_sum
from tbl;

NAME         VAL RUNNING_SUM
----- ---------- -----------
hary        2772        2772
john        4000        6772
may       2227.5      8999.5
peter        450      9449.5
peter        270      9719.5
peter        270      9989.5
peter         90     10079.5
sia        20000     30079.5
steve       2000     32079.5
tom          500     32579.5

The order that the rows for the same name are evaluated is still indeterminate, since there is no instruction in that analytic clause on how to break ties.

The entire result is now implicitly ordered (at least today, using a CTE and with my version and my optimiser's decisions), which might not be what you want; but then you should have an explicit order by anyway if the order does matter to you, whatever it is.

My logic about average was flawed. – Sunny KC Dec 14 '18 at 16:19 — Sunny KC, Dec 14 '18 at 16:19

score 0 · Answer 2 · answered Dec 14 '18 at 12:46

check my answer.

I added one more subquery for row_number and sorted upon it

with tbl as 
(
select 'steve' name, 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
), tbl2 as
(
  select row_number() over (order by 1) rn, tbl.* from tbl
)
select name, val,
        sum(val) over (order by rn) running_sum
from tbl2;

score 0 · Answer 3 · answered Dec 14 '18 at 16:31

0

So, whats actually happening is that the analytic function first aggregates the total for a window, in this case for peter which is 1080, then adds the result to the preceding rows total, 8999.5, and finally displays the total (1080 + 8999.5 = 10079.5) for each rows in the window.

There's nothing to do with average for the window.

answered Dec 14 '18 at 16:31

Sunny KC

235
2
6

Just summed it up. – Sunny KC Dec 14 '18 at 16:34

Analytic Function SUM returns Average for window with same values

3 Answers3