All Forums Database
teradatauser2 236 posts Joined 04/12
01 Jun 2012
aggregation error

 

I have a table taba with 3 columns a,b,c. 

 

my query is

 

sel avg(c) as colavg

      case when colavg <= c then 'less' else 'more' end

from taba

 

However, this is not wroking. It gives an error for aggregation error ( the standard aggreagation error that we get non-aggregated column is not part of the aggregated group..something of this sort).

 

Basically i want to calculate the average of all the columns and compare each column value with the avg. Taba is not a dierct table, i have coded sql to get that table.

 

There is no possiblilty of storing this data to another table and then comparing. I need to do this in the same sql.

 

 

ulrich 816 posts Joined 09/09
01 Jun 2012

check the OLAP version of AVG

select day_of _calendar, 
       avg(day_of _calendar) over () as avg_day, 
       case when day_of _calendar < avg_day then 'less' else 'more' end  
from sys_calendar.Calendar 
where year_of _calendar = 2012 
           and month_of _year = 6;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Jim Chapman 449 posts Joined 09/04
01 Jun 2012

with the_avg(colavg) as (select avg(c)from taba)
   select case when colavg <= c then 'less' else 'more' end
     from taba, the_avg ;

 

teradatauser2 236 posts Joined 04/12
02 Jun 2012

 

Hi Ulrich,

 

avg(day_of _calendar) over () as avg_day, this is the MAVG function ANSI equivlent i believe.

 

I have few questions :

 

1. This function is used to calculate the moving average rt and we need to specify a window ?

 

2. When you don't specify anything like ROWS N PRECEDING in the over(), then does it take all the rows ? It means i will have the avg of all the rows in the table and i can use it in the case below. And this would not give me the aggregation error that i got earlier.

 

3.

can i not use the code like this. i dont need to select the average actually.

 

select 

  

       case when avg(day_of _calendar) < avg_day then 'less' else 'more' end 

 

from sys_calendar.Calendar 

where year_of _calendar = 2012 

           and month_of _year = 6;

 

Sorry, but i have not tried the code yet, so asking the questions

 

ulrich 816 posts Joined 09/09
04 Jun 2012

I like to quote dieter her

"Whom do you believe?

Your first hand experience when you actuallly tried ... or some braindumps or other dubious sources?"

Try and read the documenation ;->

But some comments on your questions:

1. yes, in () you can add some more options -> read the manual.

2. yes, but beside oder by you can also specify partition by which, again read the manual.

3. try it

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

teradatauser2 236 posts Joined 04/12
05 Jun 2012

Thanks Ulrich..your suggested code worked !!

You must sign in to leave a comment.