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