15.00 - CSUM - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

CSUM

Purpose  

Returns the cumulative (or running) sum of a value expression for each row in a partition, assuming the rows in the partition are sorted by the sort_expression list.

Type

Teradata-specific function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression

a numeric literal or column expression for which a running sum is to be computed.

By default, CSUM uses the default data type of value_expression. Larger numeric values are supported by casting it to a higher data type.

The expression cannot contain any ordered analytical or aggregate functions.

sort_expression

a literal or column expression or comma-separated list of literal or column expressions to be used to sort the values.

For example, CSUM(Sale, Region ASC, Store DESC), where Sale is the value_expression, and Region ASC, Store DESC is the sort_expression list.

The expression cannot contain any ordered analytical or aggregate functions.

ASC

ascending sort order.

The default sort direction is ASC.

DESC

descending sort order.

ANSI Compliance

CSUM is a Teradata extension to the ANSI SQL:2011 standard.

Using SUM Instead of CSUM

The use of CSUM is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard, and is equivalent to the ANSI-compliant SUM window function that specifies ROWS UNBOUNDED PRECEDING as its aggregation group. CSUM is retained only for backward compatibility with existing applications.

For more information on the SUM window function, see “Window Aggregate Functions” on page 984.

Meaning of Cumulative Sums

CSUM accumulates a sum over an ordered set of rows, providing the current value of the SUM on each row.

Possible Result Overflow with SELECT Sum

When using this function, the result can create an overflow when the data type and format are not in sync. For a column defined as:

Salary Decimal(15,2) Format ‘$ZZZ,ZZ9.99’

The following query:

SELECT SUM (Salary) FROM Employee;

causes an overflow because the decimal operand and the format are not in sync.

To avoid possible overflows, explicitly specify the format for decimal sum to specify a format large enough to accommodate the decimal sum resultant data type.

SELECT Sum(Salary) (format ‘$Z,ZZZ,ZZZ,ZZ9.99) FROM Employee;

Result Type and Attributes

The data type, format, and title for CSUM are as follows:

Data Type: Same as operand x

  • If operand x is character, the format is the default format for FLOAT.
  • If operand x is numeric, the format is the same format as x.
  • For information on the default format of data types and an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Example  

    Report the daily running sales total for product code 10 for each month of 1998.

       SELECT cmonth, CSUM(sumPrice, cdate)
       FROM 
       (SELECT a2.month_of_year,
       a2.calendar_date,a1.itemID, SUM(a1.price)
       FROM Sales a1, SYS_CALENDAR.Calendar a2
       WHERE a1.calendar_date=a2.calendar_date
       AND a2.calendar_date=1998
       AND a1.itemID=10
       GROUP BY a2.month_of_year, a1.calendar_date,
       a1.itemID) AS T1(cmonth, cdate, sumPrice)
       GROUP BY cmonth;

    Grouping by month allows the total to accumulate until the end of each month, when it is then set to zero for the next month. This permits the calculation of cumulative totals for each item in the same query.

    Example  

    Provide a running total for sales of each item in store 5 in January and generate output that is ready to export into a graphing program.

       SELECT Item, SalesDate, CSUM(Revenue,Item,SalesDate) AS CumulativeSales 
       FROM 
       (SELECT Item, SalesDate, SUM(Sales) AS Revenue
       FROM DailySales
       WHERE StoreId=5 AND SalesDate BETWEEN 
       '1/1/1999' AND '1/31/1999'
       GROUP BY Item, SalesDate) AS ItemSales
       ORDER BY SalesDate;

    The result might like something like the following table:

     

    Item

    SalesDate

    CumulativeSales

    InstaWoof dog food

    01/01/1999

    972.99

    InstaWoof dog food

    01/02/1999

    2361.99

    InstaWoof dog food

    01/03/1999

    5110.97

    InstaWoof dog food

    01/04/1999

    7793.91