All Forums Database
gnanareddy 19 posts Joined 03/14
31 Aug 2015
Aggregation

Hi,
Could you please help on SQL query for below requirement.
Source Data:

COL1	COL2	COL3	COL4
101	ABCD	08-14-2015	0
101	ABCD	08-07-2015	1
101	ABCD	07-31-2015	1.22
101	ABCD	07-24-2015	0
101	ABCD	07-17-2015	0.33
101	ABCD	07-10-2015	1.11

Expected output:
Column 1 and 2 are same. For every date value in column3 would like to aggregate 4 column4 (COL4) values and keep the aggregated value in COL4.
eg: COL1 COL2       COL3           COL4     
     101  ABCD 08-14-2015 SUM(0,1,1.22,0)
     101  ABCD 08-14-2015 SUM(1,1.22,0,0.33)

COL1	COL2	COL3	COL4
101	ABCD	08-14-2015	2.22
101	ABCD	08-07-2015	2.55
101	ABCD	07-31-2015	2.66
101	ABCD	07-24-2015	1.44
101	ABCD	07-17-2015	1.44
101	ABCD	07-10-2015	1.11

Thank you ,
Regards,
Gnana Reddy.

dnoeth 4628 posts Joined 11/04
31 Aug 2015

Hi Gnana Reddy,
you want a moving sum:

  sum(col4)
  over (partition by col1,col2
        order by col3 
        rows between 3 preceding and current row)

 

Dieter

sakthikrr 53 posts Joined 07/12
31 Aug 2015

Perfect Dieter!
This is ordered analytical function called "Rows unbounded preceding" in Teradata.

SELECT col1, col2, col3, sum(col4) OVER (ORDER BY col3 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
FROM <table>
ORDER BY col3;

Hope this helps!

Sakthi

sakthikrr 53 posts Joined 07/12
31 Aug 2015

++ partition by clause

SELECT col1, col2, col3, sum(col4) OVER (partition by col1, col2 ORDER BY col3 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
FROM <table>
ORDER BY col3;

 

Sakthi

gnanareddy 19 posts Joined 03/14
01 Sep 2015

Thank You very much Dnoeth, working perfectly..

You must sign in to leave a comment.