• If you are using SUM function and want to calculate the cumulative SUM, then just specify ORDER BY clause with ROWS UNBOUNDED PRECEDING which would give the same result as that of CSUM function.
• To compute moving average using SUM window function use ORDER BY clause and specify ROWS number PRECEDING (number of rows preceding the current row).
Let’s work out some examples using the data present in the previous section.
Scenario 1:- Calculate the cumulative actual sales per store ordered by sale month:
SELECT store_no, sale_month, actual_sale,
SUM (actual_sale) OVER (PARTITION BY store_no ORDER BY sale_month ROWS UNBOUNDED PRECEDING) as StoreTotal
FROM sales;ORDER BY store_no, sale_month;
Result Set:-
store_no sale_month actual_sale StoreTotal
10 01/01/2007 13,500 13,500
10 05/01/2007 13,500 27,000
20 02/01/2007 11,000 11,000
20 04/01/2007 12,500 23,500
30 02/01/2007 10,000 10,000
30 03/01/2007 12,500 22,500
30 03/01/2007 10,500 33,000
40 02/01/2007 11,500 11,500
40 04/01/2007 12,500 24,000
50 01/01/2007 10,500 10,500
50 05/01/2007 12,500 23,000
60 06/01/2007 11,500 11,500
70 07/01/2007 11,500 11,500
80 07/01/2007 15,500 15,500
90 06/01/2007 15,500 15,500
Scenario 2:- Calculate the total actual sales of fruits by category per each store:
SELECT store_no, prod_code, actual_sale,
SUM (actual_sale) OVER (PARTITION BY prod_code ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as FruitTotal
FROM sales;
Result would be as follows:
store_no prod_code actual_sale FruitTotal
60 Apple 11,500 25,000
10 Apple 13,500 25,000
40 Apricot 12,500 26,000
10 Apricot 13,500 26,000
20 Banana 11,000 22,500
70 Banana 11,500 22,500
50 Grapes 12,500 28,000
80 Grapes 15,500 28,000
50 Guava 10,500 22,000
40 Guava 11,500 22,000
20 Mango 12,500 25,000
30 Mango 12,500 25,000
90 Orange 15,500 26,000
30 Orange 10,500 26,000
30 Strawberry 10,000 10,000
Scenario 3:- Calculate the moving actual sales per month per store.
SELECT store_no, sale_month, actual_sale,
SUM (actual_sale) OVER (PARTITION BY store_no, sale_month ORDER BY actual_sale ROWS 1 PRECEDING) as SaleTrend
FROM sales;
And the Result would be:
store_no sale_month actual_sale SaleTrend
10 01/01/2007 13,500 13,500
10 05/01/2007 13,500 13,500
20 02/01/2007 11,000 11,000
20 04/01/2007 12,500 12,500
30 02/01/2007 10,000 10,000
30 03/01/2007 10,500 10,500
30 03/01/2007 12,500 23,000
40 02/01/2007 11,500 11,500
40 04/01/2007 12,500 12,500
50 01/01/2007 10,500 10,500
50 05/01/2007 12,500 12,500
60 06/01/2007 11,500 11,500
70 07/01/2007 11,500 11,500
80 07/01/2007 15,500 15,500
90 06/01/2007 15,500 15,500
Our Random Articles
- Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
- How To Choose A Good Online Traffic School
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
More Links
4 Comments
Material that you are providing is very easy to learn. But it is much better than this , if you ll give the full material of this at ONE LINK.
Thank you
Suresh.
That is right Suresh. This is the reason I have given all the links in one page, please visit this
http://readvitamin.com/teradata/
Hope this helps.
Hi. Can anyone tell me how to extract time from timestamp?
For Example
timestamp=’2008-03-28 14:11:04′
i want time ’14:11:04′ from timestamp through query.
Thanks in advance.
Hi Vaideesh,
If you need only time, you can probably try the following;
SELECT time;
To answer your question you should do something like this.
SELECT CAST(VaideeshTime as time(0)) from timestamp;
VaideeshTime
——–
14:11:04
Hope that helps.