SQL statement to update a column

Question

I have a Table T1 with following values

I need a result table with additional column which is the average of upto date. i.e.,

x1= 1000.45

x2= (1000.45+2000.00)/2

x3= (1000.45+2000.00+3000.50)/3

x4= (1000.45+2000.00+3000.50+4000.24)/4

The result table should look like the following:

I need to write SQL statement in Oracle database to add a column to result table with column values x1, x2, x3, x4.

"I need to write SQL statement" yeah cool, so what have you written? — domdomcodecode, Oct 30 '15 at 04:30
@Gonzalo Thank u for editing my question. I'm new here and does not know the format — user5505661, Oct 30 '15 at 04:30
See if [this article](http://www.adp-gmbh.ch/ora/sql/analytical/rollingavg.html) gives you some ideas. — PM 77-1, Oct 30 '15 at 04:34
Please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) We need sample data and desire output to find an answer faster, otherwise we will be wasting time guessing what you need. You also could create your schema with data using **http://www.sqlfiddle.com** . Even when pictures are cool is very hard to transcript so include code text. — Juan Carlos Oropeza, Oct 30 '15 at 04:38

WW. · Accepted Answer · 2015-10-30T04:43:51.783

1

You need to use an analytic function for this. My untested SQL is as follows:

SELECT
 date,
 division,
 sum_sales,
 AVG( sum_sales ) OVER ( ORDER BY date ROWS UNBOUNDED PRECEDING )
FROM
 table;

date is a reserved word in Oracle, so if you are using that as your real column name you will need to include it in quotes.

edited Oct 30 '15 at 04:43

answered Oct 30 '15 at 04:35

WW.

23,793
13
94
121

1

@anwaar_hell No, there is no need for a GROUP BY with an analytic function. There is not grouping because every original row is represented in the result. – WW. Oct 30 '15 at 04:42

score 1 · Answer 2 · answered Oct 30 '15 at 04:35

1

select date,division,sum_sales,avg(sum_sales) over ( order by sum_sales ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from table
group by date,division,sum_sales

answered Oct 30 '15 at 04:35

anwaar_hell

756
5
23

score 1 · Answer 3 · edited May 23 '17 at 10:29

You need to use AVG function OVER ordering by date. As each row is an aggregation result of all the preceding rows, you need to define the window of the aggregation as UNBOUNDED PRECEDING

By following these guidelines, the resultant statement would be like:

SELECT date_d, 
       division, 
       sum_sales, 
       AVG(sum_sales) 
         over ( 
           ORDER BY date_d ROWS unbounded preceding ) avrg 
FROM   supplier;

You can test that in FIDDLE

Good two pieces of information about analytical functions in these two articles:
Introduction to Analytic Functions (Part 1)
Introduction to Analytic Functions (Part 2)

SQL statement to update a column

3 Answers3