Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

generating running totals

Rob JonesNov 11 2014 — edited Nov 11 2014

Hi, using Oracle 11g r2.

I know how to generate running totals like using the following query. However, I need to add a quantity to the first value of every group, then generate the running total.

SELECT EMPloyee_id,

               upper(employee_title),

               SAL,

               SUM(SAL) OVER(PARTITION BY upper(employee_title) ORDER BY EMPloyee_id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL

FROM   EMPloyee

The following output is produced

empID title salary running_total

1 DEVELOPER 1000 1000

2 DEVELOPER 1000 2000

7 DEVELOPER 1000 3000

10 DEVELOPER 1000 4000

3 DEVELOPMENT MANAGER 1000 1000

5 PROD SUPPORT 1000 1000

6 PROD SUPPORT 1000 2000

8 PROD SUPPORT 1000 3000

9 PROD SUPPORT 1000 4000

Required output

empID title salary running_total

1 DEVELOPER 1000 2000(add 1000 to first quantity)

2 DEVELOPER 1000 3000

7 DEVELOPER 1000 4000

10 DEVELOPER 1000 5000

3 DEVELOPMENT MANAGER 1000 2000   (add 1000 to first quantity)

5 PROD SUPPORT 1000 2000 (add 1000 to first quantity)

6 PROD SUPPORT 1000 3000

8 PROD SUPPORT 1000 4000

9 PROD SUPPORT 1000 5000

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 9 2014
Added on Nov 11 2014
2 comments
184 views