Netezza Cumulative Sum, Average and Example

You can make use of the Netezza analytical functions to calculate the cumulative sum or running sum. Sum and Average analytical functions are used along with window options to calculate the Netezza cumulative sum or running sum.

Read:

• Identify and Remove Netezza Duplicate Records in Table
• IBM Netezza Extract Numbers from String Examples
• Netezza Pivot Rows to Column With Example
• Netezza Update Join Syntax and Examples
• Netezza Recursive Query Alternative and Examples
• IBM Netezza Rollup Group Aggregates using Grouping sets 

Netezza Cumulative Sum, Average Syntax:

Below are the Syntax for Netezza Cumulative SUM, AVG analytic functions. You can use these functions in queries to identify the cumulative SUM or AVG based on your requirements.

SUM(column | expression)
 OVER( PARTITION BY columns 
 ORDER BY sort columns rows unbounded preceding |
 rows between <N> preceding and current row
 )

AVG(column | expression)
 OVER( PARTITION BY columns 
 ORDER BY sort columns rows unbounded preceding |
 rows between <N> preceding and current row 
 )

Netezza Cumulative Sum, Average Examples

Below are some of the examples on calculating Netezza Cumulative Sum and Average. For cumulative SUM, you will either have to use “unbounded preceding” or “unbounded following”. Below example demonstrates usage of “unbounded preceding” to consider preceding result for cumulative calculation.

Query:

select
SALE_QTY,
SUM(SALE_QTY)
 OVER( PARTITION BY NULL
 ORDER BY SALE_QTY ASC rows unbounded preceding
 ) "Cumulative Sum",
AVG(SALE_QTY)
 OVER( PARTITION BY NULL
 ORDER BY SALE_QTY ASC rows unbounded preceding
 ) "Cumulative Average"
from SAMPLE_SALES;

Output:

 SALE_QTY | Cumulative Sum | Cumulative Average 
----------+----------------+--------------------
 123457 | 123457 | 123457.000000
 232253 | 355710 | 177855.000000
 265646 | 621356 | 207118.666667
 265654 | 887010 | 221752.500000
 267156 | 1154166 | 230833.200000
 897456 | 2051622 | 341937.000000
 1268957 | 3320579 | 474368.428571
(7 rows)

You can limit the cumulative sum to only N preceding or following rows based on your requirements. For example, limit cumulative sum to current row and previous two rows. The query and the output is as below:

Query:

select
SALE_QTY,
SUM(SALE_QTY)
 OVER( PARTITION BY NULL
 ORDER BY SALE_QTY ASC rows between 2 preceding and current row
 ) "Cumulative Sum",
AVG(SALE_QTY)
 OVER( PARTITION BY NULL
 ORDER BY SALE_QTY ASC rows between 2 preceding and current row
 ) "Cumulative Average"
from SAMPLE_SALES;

Output:

 SALE_QTY | Cumulative Sum | Cumulative Average 
----------+----------------+--------------------
 123457 | 123457 | 123457.000000
 232253 | 355710 | 177855.000000
 265646 | 497899 | 248949.500000
 265654 | 531300 | 265650.000000
 267156 | 532810 | 266405.000000
 897456 | 1164612 | 582306.000000
 1268957 | 2166413 | 1083206.500000
(7 rows)