Home » SQL & PL/SQL » SQL & PL/SQL » Which aggregate function should use for this problem
Which aggregate function should use for this problem [message #318559] Wed, 07 May 2008 03:12 Go to next message
sunilgond
Messages: 35
Registered: January 2008
Location: INDIA
Member

Hi

i want to agrrgate the number from bottom to top like

result
1 15
2 14
3 12
4 9
5 5

Please tell me how can i achieve this
Re: Which aggregate function should use for this problem [message #318562 is a reply to message #318559] Wed, 07 May 2008 03:15 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
order by ... descending
Re: Which aggregate function should use for this problem [message #318567 is a reply to message #318562] Wed, 07 May 2008 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your example explains almost nothing, so I've written you a nice complex query to generate exactly the numbers you displayed.

select num
      ,15 - sum(num-1) over (order by num rows between unbounded preceding and current row) tot
from (
select level num from dual connect by level <=5)
Re: Which aggregate function should use for this problem [message #318644 is a reply to message #318567] Wed, 07 May 2008 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Wed, 07 May 2008 04:25
Your example explains almost nothing, so I've written you a nice complex query to generate exactly the numbers you displayed.



But your query doesn't have the heading "result" with two separate columns listed under it Wink
Re: Which aggregate function should use for this problem [message #318647 is a reply to message #318567] Wed, 07 May 2008 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I received some more details by PM. Why the OP didn't simply post them, I just don't know:
Quote:
Hi

I want to add the number to the upper one from descending to ascending one

result
1 6 (5+1)
2 5 (3+2)
3 3


Rewriting this backwards, I think we're looking for:
Value   Result
n       n
n-1     n+(n-1)
n-2     n+(n-1)+(n-2)
...
3       n+(n-1)+(n-2)+...+3
2       n+(n-1)+(n-2)+...+3+2
1       n+(n-1)+(n-2)+...+3+2+1


A simple mod to the previous query to sum from the current value to the highest should do it:
select num
      ,sum(num) over (order by num rows between current row and unbounded following) tot
from (
select level num from dual connect by level <= x)

Re: Which aggregate function should use for this problem [message #318648 is a reply to message #318647] Wed, 07 May 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Wed, 07 May 2008 08:55
I received some more details by PM. Why the OP didn't simply post them, I just don't know:



then I have to hand it to you JRowbottom, your original code was oh so close to the solution based on nothing other than output. Amazing! It's like those Mensa questions.
Re: Which aggregate function should use for this problem [message #318653 is a reply to message #318648] Wed, 07 May 2008 08:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thank you kindly.
Pure fluke, but an impressive fluke
Previous Topic: hi............
Next Topic: capturing user details
Goto Forum:
  


Current Time: Tue Dec 06 12:34:26 CST 2016

Total time taken to generate the page: 0.14225 seconds