How to create a loop

Database
Enthusiast

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.

5 REPLIES
Enthusiast

Re: How to create a loop

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

Senior Apprentice

Re: How to create a loop

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
Enthusiast

Re: How to create a loop

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

Enthusiast

Re: How to create a loop

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

Enthusiast

Re: How to create a loop

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.