15.00 - CSUM - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)
Last Update



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.


Teradata-specific function.




Syntax element …

Specifies …


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.


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.


ascending sort order.

The default sort direction is ASC.


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.


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

       SELECT cmonth, CSUM(sumPrice, cdate)
       (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.


    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 
       (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:





    InstaWoof dog food



    InstaWoof dog food



    InstaWoof dog food



    InstaWoof dog food