CSUM Function Examples | VantageCloud Lake - CSUM Function Examples - Teradata Vantage

Report Daily Sales for Product 10 in Each Month of 1998

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 the total is set to zero for the next month. This permits the calculation of cumulative totals for each item in the same query.

Report Running Sales Total of Each Item in Store 5 in January

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 may look like the following:

               Item   Sales Date   Cumulative Sales
-------------------   ----------   ----------------
Insta Woof dog food   01/01/1999             972.99
Insta Woof dog food   01/02/1999            2361.99
Insta Woof dog food   01/03/1999            5110.97
Insta Woof dog food   01/04/1999            7793.91