Need to write a query to generate average sales every 2 years

Database

Need to write a query to generate average sales every 2 years

I have a table with 2 columns as shown below. Year and sales in that year

 

Year      Sales

1990     2000

1991     3000

1992     2500

1993     3500

1994     4000

1995      6000

 

I Need to write a query which will display Average sales for a period of 2 years and same should be displayed for those 2 years for which sales is averaged.

 

Year         Avg(sales)for 2 years

1990         2500

1991         2500

1992         3000

1993         3000

1994         5000

1995         5000

 

1 REPLY
Teradata Employee

Re: Need to write a query to generate average sales every 2 years

Hello Arjun,

 

plese have a look on the below SQL .

 

SyntaxEditor Code Snippet

sel yr,case when 
(row_number () over (order by yr) )mod 2 =0 
then avg(amount) over ( order by yr   rows between 1 preceding and current row )else avg(amount) over ( order by yr   rows between current  row and 1 following   )end as  t
from dev_test
order by 1 
Tags (1)