Aggregation

Database
Enthusiast

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.

Tags (1)
9 REPLIES
Junior Contributor

Re: Aggregation

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)
Enthusiast

Re: Aggregation

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!

Enthusiast

Re: Aggregation

++ 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;
Enthusiast

Re: Aggregation

Thank You very much Dnoeth, working perfectly..

Fan

Re: Aggregation

I will like to create a column containing aggregate values of other columns. See the code below:

 

SyntaxEditor Code Snippet

select 
 sub_activation_wk ,sum (ia_sum)  A1 ,sum (nir_sum)  A2 ,sum (gam_sum)  A3 ,sum (nid_sum)  A4,sum('A1', 'A2', 'A3', 'A4')  gross_add
 
 from prepaid_model4 
************************************
I get an error in the last line. What is the proper syntax to create this column?

 

Teradata Employee

Re: Aggregation

A1+A2+A3+A4
(Without the SUM)
Fan

Re: Aggregation

Thanks Todd.

When I run this

SyntaxEditor Code Snippet

 sub_activation_wk ,sum (ia_sum)  A1 ,sum (nir_sum)  A2 ,sum (gam_sum)  A3 ,sum (nid_sum)  A4,A1+ A2+ A3-A4 AS  gross_add

I don't get any value in gross_add. result setresult set

Junior Contributor

Re: Aggregation

You need to change NULL to zero using COALESCE:

 

COALESCE(A1,0) + COALESCE(A2,0) + COALESCE(A3,0) - COALESCE(A4,0) AS  gross_add  

 

Fan

Re: Aggregation

Thank you very much, dnoeth.