CODEWITHSUNDEEP
sqldatabaseoracle-databasesql-update
user5505661
user5505661

Reputation: 69

SQL statement to update a column

I have a Table T1 with following values

Table

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:

expected result

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

Upvotes: 2

Views: 92

Answers (3)

Hawk
Hawk

Reputation: 5170

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)

Upvotes: 1

WW.
WW.

Reputation: 24271

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.

Upvotes: 1

anwaar_hell
anwaar_hell

Reputation: 776

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

Upvotes: 1

Related Questions