Home » SQL & PL/SQL » SQL & PL/SQL » avg( ) over (partition by )
avg( ) over (partition by ) [message #198756] Wed, 18 October 2006 08:00 Go to next message
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
Re: avg( ) over (partition by ) [message #198762 is a reply to message #198756] Wed, 18 October 2006 08:07 Go to previous messageGo to next message
joy_division
Messages: 4643
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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.
Re: avg( ) over (partition by ) [message #198767 is a reply to message #198756] Wed, 18 October 2006 08:16 Go to previous messageGo to next message
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?



Re: avg( ) over (partition by ) [message #198777 is a reply to message #198767] Wed, 18 October 2006 08:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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.
Re: avg( ) over (partition by ) [message #198779 is a reply to message #198767] Wed, 18 October 2006 08:48 Go to previous messageGo to next message
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;


Re: avg( ) over (partition by ) [message #198781 is a reply to message #198777] Wed, 18 October 2006 08:50 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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.
Previous Topic: SQL query doubt ..
Next Topic: could trigger achieve the function?
Goto Forum:
  


Current Time: Fri Dec 09 11:45:11 CST 2016

Total time taken to generate the page: 0.12718 seconds