SUM ():- When used with partition clause, SUM () function returns the cumulative or moving sum of an expression based on how the aggregation group is specified. This function can also be used in place of CSUM and MSUM which are Teradata-specific functions and are discouraged to a great extent. As per recent recommendations from Teradata, usage of ANSI-compliant window function for any new applications is advised. Lets see why Teradata suggests to go for ANSI-compliant SUM () function.

• If you are using SUM function and want to calculate the cumulative SUM, then just specify ORDER BY clause with ROWS UNBOUNDED PRECEDING which would give the same result as that of CSUM function.

• To compute moving average using SUM window function use ORDER BY clause and specify ROWS number PRECEDING (number of rows preceding the current row).

Let’s work out some examples using the data present in the previous section.

Scenario 1:- Calculate the cumulative actual sales per store ordered by sale month:

SELECT store_no, sale_month, actual_sale,

SUM (actual_sale) OVER (PARTITION BY store_no ORDER BY sale_month ROWS UNBOUNDED PRECEDING) as StoreTotal

FROM sales;ORDER BY store_no, sale_month;

Result Set:-

store_no   sale_month         actual_sale   StoreTotal

10         01/01/2007            13,500          13,500

10         05/01/2007            13,500          27,000

20         02/01/2007            11,000          11,000

20         04/01/2007            12,500          23,500

30         02/01/2007            10,000          10,000

30         03/01/2007            12,500          22,500

30         03/01/2007            10,500          33,000

40         02/01/2007            11,500          11,500

40         04/01/2007            12,500          24,000

50         01/01/2007            10,500          10,500

50         05/01/2007            12,500          23,000

60         06/01/2007            11,500          11,500

70         07/01/2007            11,500          11,500

80         07/01/2007            15,500          15,500

90         06/01/2007            15,500          15,500

Scenario 2:- Calculate the total actual sales of fruits by category per each store:

SELECT store_no, prod_code, actual_sale,

SUM (actual_sale) OVER (PARTITION BY prod_code ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as FruitTotal

FROM sales;

Result would be as follows:

store_no   prod_code   actual_sale   FruitTotal

60         Apple            11,500           25,000

10         Apple            13,500           25,000

40         Apricot          12,500           26,000

10         Apricot          13,500           26,000

20         Banana          11,000           22,500

70         Banana          11,500           22,500

50         Grapes           12,500           28,000

80         Grapes           15,500           28,000

50         Guava            10,500           22,000

40         Guava            11,500           22,000

20         Mango            12,500          25,000

30         Mango            12,500          25,000

90         Orange           15,500          26,000

30         Orange           10,500          26,000

30         Strawberry      10,000          10,000

Scenario 3:- Calculate the moving actual sales per month per store.

SELECT store_no, sale_month, actual_sale,

SUM (actual_sale) OVER (PARTITION BY store_no, sale_month ORDER BY actual_sale ROWS 1 PRECEDING) as SaleTrend

FROM sales;

And the Result would be:

store_no   sale_month         actual_sale   SaleTrend

10         01/01/2007           13,500          13,500

10         05/01/2007           13,500          13,500

20         02/01/2007           11,000          11,000

20         04/01/2007           12,500          12,500

30         02/01/2007           10,000          10,000

30         03/01/2007           10,500          10,500

30         03/01/2007           12,500          23,000

40         02/01/2007           11,500          11,500

40         04/01/2007           12,500          12,500

50         01/01/2007           10,500          10,500

50         05/01/2007           12,500          12,500

60         06/01/2007           11,500          11,500

70         07/01/2007           11,500          11,500

80         07/01/2007           15,500          15,500

90         06/01/2007           15,500          15,500

Popularity: 38% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links