OraFAQ Forum: SQL & PL/SQL » avg( ) over (partition by )

Home » SQL & PL/SQL » SQL & PL/SQL » avg( ) over (partition by )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

avg( ) over (partition by ) [message #198756]

Wed, 18 October 2006 08:00

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hello

I want to calculate the average from P1 where the date is > 200101 (yyyymm). How can I do that with a "partition by" statement? The code below works if I leave away "where xdate > 200101". How can I write the where clause in the partition clause?

select xdate,
       P1,
       avg(P1) over (partition by xdate where xdate > 200101)
from tab

Thanks
Stefan

Report message to a moderator

Re: avg( ) over (partition by ) [message #198762 is a reply to message #198756]

Wed, 18 October 2006 08:07

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

steffeli wrote on Wed, 18 October 2006 09:00

select xdate,
       P1,
       avg(P1) over (partition by xdate where xdate > 200101)
from tab

Thanks
Stefan

Just move the where clause out of the partition statement as such:

select xdate, P1,
       avg(P1) over (partition by xdate)
from tab
where xdate > 200101;

Whoops, fixed typo.

[Updated on: Wed, 18 October 2006 08:22]

Report message to a moderator

Re: avg( ) over (partition by ) [message #198763 is a reply to message #198756]

Wed, 18 October 2006 08:07

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Hi Stefan,
I really didn't understand your requirement.

Are you looking for the following?

select xdate,
       P1,
       avg(P1) over (partition by xdate)
from tab
where xdate > 200101;

I don't think so.

What exactly you want to show avg(P1) when the xdate <= 200101?
Do you want to display the row or not?

Can you please provide some sample data and output?

By
Vamsi.

Report message to a moderator

Re: avg( ) over (partition by ) [message #198767 is a reply to message #198756]

Wed, 18 October 2006 08:16

steffeli
Messages: 112
Registered: July 2006

Senior Member

The code

select xdate, P1,
       avg(P1) over (partition by xdate)
 where xdate > 200101
from tab;

still does not work, I get the error "FROM keyword not found where expected"

The solution

select xdate,
       P1,
       avg(P1) over (partition by xdate)
from tab
where xdate > 200101;

is not what I want, because the where clause is just valid for the avg(). Any other solution?

Report message to a moderator

Re: avg( ) over (partition by ) [message #198777 is a reply to message #198767]

Wed, 18 October 2006 08:43

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Hi,
Even though you didn't reply for my questions
"What exactly you want to show avg(P1) when the xdate <= 200101?
Do you want to display the row or not?"

I'm assuming that you want to display those rows but you don't want to display the avg(P1) for the rows when xdate > 200101.

If that is your requirement

select xdate,
       P1,
       avg(case when xdate > 200101 then P1 end) over (partition by xdate)
from tab

I believe assumptions lead to wrong answers / replies. Please provide little more information, if the above is not your requirement.

By
Vamsi.

Report message to a moderator

Re: avg( ) over (partition by ) [message #198779 is a reply to message #198767]

Wed, 18 October 2006 08:48

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'd try something like this, and calculate the avg from a sum and a count.

(untested)

select xdate, P1,
       sum(case when xdate >=200101 then p1 else null end) over (partition by xdate)
      /count(case when xdate >=200101 then p1 else null end) over (partition by xdate)
from tab;

Report message to a moderator

Re: avg( ) over (partition by ) [message #198781 is a reply to message #198777]

Wed, 18 October 2006 08:50

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Also if P1 is not nullable column then the above code is fine.
If not, check the below one.

select xdate,
       P1,
       avg(case when xdate > 200101 then nvl(P1,0) end) over (partition by xdate)
from tab

As the analytical functions don't consider the null valued rows, I didn't put an else in the case.

Check whichever is suitable for your requirement.

By
Vamsi.

Report message to a moderator

Previous Topic:	SQL query doubt ..
Next Topic:	could trigger achieve the function?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 21:23:03 CST 2024