Home » SQL & PL/SQL » SQL & PL/SQL » select over filter
select over filter [message #288753] Tue, 18 December 2007 10:23 Go to next message
niscsi
Messages: 4
Registered: December 2007
Junior Member

Hi

i want to use select over with "filtering"
for example:

select date,pr
From
(
select date,sum(price)
over(
order by date
rows
between
unbounded preceding
and
current row
)as pr
FROM t)
where pr=200

so this works but this in not efficient. because if i use this way the oracle never finished on my DB.

So i want to the inner clause : "sum(price)
over(
order by date
rows
between
unbounded preceding
and
current row
)as pr"
just performing when price=200

so i wanna something like this but obviously it doesn't work obviously:

select date,sum(price)
over(
case when price=200
else "doesn't perform the sum"
end
order by date
rows
between
unbounded preceding
and
current row
)as pr
FROM t

Thanks

Istvan
Re: select over filter [message #288756 is a reply to message #288753] Tue, 18 December 2007 10:26 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
All you have to do is put the case statement inside the sum(<condition>) over(...).

Regards

Raj
Re: select over filter [message #288769 is a reply to message #288753] Tue, 18 December 2007 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Don't put only one word per line.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: select over filter [message #288786 is a reply to message #288756] Tue, 18 December 2007 12:12 Go to previous messageGo to next message
niscsi
Messages: 4
Registered: December 2007
Junior Member

thanks for the answer, but i don't know how could i use the sum(<condition>) over(...)
i read the oracle reference here http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/functions120a.htm#86759
but i didn't find anything about condition, so could you give me more help.
Re: select over filter [message #288788 is a reply to message #288786] Tue, 18 December 2007 12:44 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
select case mod(rownum,2) when 0 then '*' else null end mod_val, 
sub_id, sum(case mod(rownum,2) when 0 then sub_id else 0 end) over(partition by main_id) sum_total, 
main_id, rownum from test
/
MOD_V     SUB_ID  SUM_TOTAL    MAIN_ID     ROWNUM
----- ---------- ---------- ---------- ----------
               1          2          1          1
*              2          2          1          2
               3          2          1          3
*              4          4          2          4
               5          4          2          5
*              6         14          3          6
               7         14          3          7
*              8         14          3          8
               9          0          4          9

9 rows selected.



The bits which are highlighted by '*' are included in the sum against the group main_id.

Regards

Raj

[Updated on: Tue, 18 December 2007 12:44]

Report message to a moderator

Previous Topic: seconds in day must be between 0 and 86399
Next Topic: Trim query
Goto Forum:
  


Current Time: Mon Dec 05 04:43:42 CST 2016

Total time taken to generate the page: 0.08239 seconds