aggregation error


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.


Re: aggregation error

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;

Re: aggregation error

with the_avg(colavg) as (select avg(c)from taba)

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

     from taba, the_avg ;

Re: aggregation error

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.


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


       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


Re: aggregation error

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

Re: aggregation error

Thanks Ulrich..your suggested code worked !!