CSUM in Teradata

OLAP ( Online Analytical Processing) functions helps in creating standard calculated measures like rank, aggregates, cumulative aggregates, etc. They take multiple elements from a list, apply a transformation on it, and returns a new list. These are mostly applied to numeric columns. They are highly essential for financial datasets or datasets that have a huge number of rows and columns. Oracle Teradata provides several OLAP functions to apply transformations on data. One of the important functions is the CSUM (Cumulative Sum). In this post, we will give a detailed explanation of what CSUM is and how it can be used.

What is CSUM?

CSUM computes the cumulative sum of a value expression for each row over an ordered dataset. It performs a sum on the values of each row. It is also known as calculating the running sum.

Syntax

CSum(value_expression, sort_expression_list)

Value_expression is the scalar numeric column for which the running sum should be calculated.

Sort_expression_list is a list of column names separated by commas. It specifies the columns that have to be used to sort the values. Ascending (ASC) is the default sort direction. 

The CSUM function sorts the dataset based on sort_expression_list first and then calculates the sum on the value_expression.

FREE Demo Class From Industry Experts - Enroll Now.!! Teradata Training

Example

Let’s take the below sales table and apply CSUM on it to find out the overall sale amount.

ID Category Date Amount
1 Accessories
24/08/2020

300
2 Clothing
1/09/2020

800
3 Electronics
29/08/2020
1500
4 Footwear
14/08/2020
600
5 Clothing
3/08/2020
700
6 Home Appliances
27/08/2020
3400
7 Accessories
18/07/2020
200
8 Electronics
10/09/2020
2700
9 Home Appliances
28/07/2020
4200
10 Footwear
22/08/2020
500

To find the running sum of the over sales amount, execute the below query. 

SELECT ID, category, date, amount, CSUM(amount, date) from sales;

The result of the query would be as follows.

ID Category
Date
Amount CSUM(amount, date)
7 Accessories 18/07/2020 200 200
9 Home Appliances 28/07/2020 4200 4400
5 Clothing 3/08/2020 700 5100
4 Footwear 14/08/2020 600 5700
10 Footwear 22/08/2020 500 6200
1 Accessories 24/08/2020 300 6500
6 Home Appliances 27/08/2020 3400 9900
3 Electronics 29/08/2020 1500 11400
2 Clothing 1/09/2020 800 12200
8 Electronics 10/09/2020 2700 14900

Don’t forget that we should give both the values for the CSUM function.

Sort and CSUM on the same column

We can also give the same column for ordering the table and applying CSUM. From the above example, let’s order by the amount column and apply CSUm on the same amount column. Here is the query for it.

Teradata Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

SELECT ID, category, date, amount, CSUM(amount, amount) from sales;

Below is the output for the query.

ID Category Date Amount CSUM(amount, amount)
7 Accessories 18/07/2020 200 200
1 Accessories 24/08/2020 300 500
10 Footwear 22/08/2020 500 1000
4 Footwear 14/08/2020 600 1600
5 Clothing 3/08/2020 700 2300
2 Clothing 1/09/2020 800 3100
3
Electronics
29/08/2020 1500 4600
8
Electronics
10/09/2020 2700 7300
6 Home Appliances 27/08/2020 3400 10700
9 Home Appliances 28/07/2020 4200 14900

You can observe that the table has been ordered by the amount column.

If you want to Explore more about Teradata? then read our updated article - Teradata Tutorial

Sorting by descending order

As I told earlier, the default sorting order would be ascending order. We can change it to descending order through psssing DESC in the query. Here is how we can do it.

SELECT ID, category, date, amount, CSUM(amount, amount DESC) from sales;

Here is the output we will get when we execute the above query.

ID
Category
Date Amount CSUM(amount, amount DESC)
9 Home Appliances 28/07/2020 4200 4200
6 Home Appliances 27/08/2020 3400 7600
8 Electronics 10/09/2020 2700 10300
3 Electronics 29/08/2020 1500 11800
2 Clothing 1/09/2020 800 12600
5 Clothing 3/08/2020 700 13300
4 Footwear
14/08/2020
600 13900
10 Footwear 22/08/2020 500 14400
1 Accessories 24/08/2020 300 14700
7 Accessories 18/07/2020 200 14900

Click here to get latest Teradata interview questions and answers for 2020

You can observe that the above table has been ordered by the amount column in descending order.

Subscribe to our youtube channel to get new updates..!

Conclusion

The CSUM function is more helpful than applying SUM on each row separately. For smaller tables, the difference might not be that much. But for larger tables, we can see a big difference. Now that you know how to use the CSUM function, try using it in your applications.

Teradata Training

Weekday / Weekend Batches

 Other Related articles:

Categories

SAP

Request for more information

Webinar

Register free live webinar on Linux Shell Scripting

30th October | 07:00 pm

0 Registered

John
John
Cloud Technologies & Cyber Security
John is a Post Graduate in Computer Science from Andhra University .She is currently working as an IT developer at hkr trainings.com. And he has great experience includes both IT development and operational roles. Connect with him on LinkedIn and Twitter. Thank you

WhatsApp
To Top