All Forums Database
johnsnow 10 posts Joined 01/15
09 Jan 2015
How to create a loop

I have a table that has 4 columns of Week Year and Cost of product A and cost of product B
I need it to sum up the cost by year cumulativly up to that Week. For example in week 4 of 2014 i need it to sum up all the costs of weeks 1 thru 4 in that year and store it in a new column for the sake of saying so we can call this column week_sumA I need it do the same thing for product B. It has to be able to do this for every single week in the year (week 50 would be the sum of all weeks 50 down to 1)
I also need it to be able to distinguish from year to year and repeat the process.
The end result would look something like below:
WK    Year    week_sumA   week_sumB
2       2014        80                  200
......
2      2015         65                  400
 
Im assuming a loop needs to be used but am relativley new to teradata so the syntax is unfamiliar to me.
 

hemanth.gudela 10 posts Joined 01/15
10 Jan 2015
select
        year_column
       ,week
       ,product_A
       ,product_B
       ,sum(product_A) over (partition by year_column order by week asc rows between 1 preceding and current row) as week_sumA
       ,sum(product_B) over (partition by year_column order by week asc rows between 1 preceding and current row) as week_sumB
from your_table

Assuming the column names in your table are year_column, week, product_A, product_B, the above query should give you the desired result.
-Hemanth Gudela
 

-Hemanth Gudela

dnoeth 4628 posts Joined 11/04
10 Jan 2015

This is a simlpe task for Standard SQL's Windowed Aggregate Functions, you need a cumulative sum:

select
   WK
  ,Year
  ,sum(costA) 
   over (partition by year
         order by wk
         rows unbounded preceding) as week_sumA
  ,sum(costB) 
   over (partition by year
         order by wk
         rows unbounded preceding) as week_sumB 
from tab
order by year, week

 

Dieter

hemanth.gudela 10 posts Joined 01/15
10 Jan 2015

Yes, the query that Deiter has posted will work.
I was just about to correct my query from 1 preceding to unbounded preceding

select
        year_column
       ,week
       ,product_A
       ,product_B
       ,sum(product_A) over (partition by year_column order by week asc rows between unbounded preceding and current row) as week_sumA
       ,sum(product_B) over (partition by year_column order by week asc rows between unbounded preceding and current row) as week_sumB
from your_table

 
-Hemanth Gudela

-Hemanth Gudela

Rohan_Sawant 55 posts Joined 07/14
10 Jan 2015

Hi Johnsnow,

In case you have more than one entry for a particular week you could modify Dieter's query as follows:
 

----Creating test data
CREATE MULTISET VOLATILE TABLE VT_TEST_DATA
(
	WK INTEGER,
	"YEAR" INTEGER,
	A INTEGER,
	B INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TEST_DATA VALUES (1,2014,20,10);
INSERT INTO VT_TEST_DATA VALUES (2,2014,30,10);
INSERT INTO VT_TEST_DATA VALUES (2,2014,10,40);
INSERT INTO VT_TEST_DATA VALUES (3,2014,5,90);
INSERT INTO VT_TEST_DATA VALUES (4,2014,15,50);

INSERT INTO VT_TEST_DATA VALUES (1,2013,0,10);
INSERT INTO VT_TEST_DATA VALUES (1,2013,120,60);
INSERT INTO VT_TEST_DATA VALUES (2,2013,15,30);
INSERT INTO VT_TEST_DATA VALUES (3,2013,45,25);
----Creation of test data completed

SELECT
   "YEAR"
  , WK
  ,SUM(A) 
   OVER (PARTITION BY "YEAR"
         ORDER BY wk
         ROWS UNBOUNDED PRECEDING) AS week_sumA
  ,SUM(B) 
   OVER (PARTITION BY "YEAR"
         ORDER BY wk
         ROWS UNBOUNDED PRECEDING) AS week_sumB 
FROM 
(
	SELECT
	"YEAR"
  , WK
  , SUM(A) AS A
  , SUM(B) AS B
 	FROM
		VT_TEST_DATA
	GROUP BY 1,2
) A
ORDER BY "YEAR", wk;

 
Thanks,
Rohan Sawant

johnsnow 10 posts Joined 01/15
10 Jan 2015

thanks all for the quick response  I used hemath's code my table didn't already have the weeks in ascending order which was causing it to sum up the sales in a non chronological order
john s. 

You must sign in to leave a comment.