Home » SQL & PL/SQL » SQL & PL/SQL » problem in SUM
problem in SUM [message #272000] Wed, 03 October 2007 11:00 Go to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
hello all,

I have a table test.

a 1000
b 2000
c 3000
d 4000

i want to sum to all number. i know there is SUM function but i want a+b=3000 + c=6000.

means show first two numbers after that 3 number will be added and so on.

thanks and regards
Anamika
Re: problem in SUM [message #272001 is a reply to message #272000] Wed, 03 October 2007 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>means show first two numbers after that 3 number will be added and so on.
Any RDBMS is like a basket full of balls.
Which ball is the 1st ball?
Re: problem in SUM [message #272002 is a reply to message #272001] Wed, 03 October 2007 11:05 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
line wise
ascending order..a,b,c,d like this

any function in SQL
Re: problem in SUM [message #272008 is a reply to message #272000] Wed, 03 October 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select chr(ascii('a')+level-1) c1, 1000*level c2 from dual connect by level <= 4)
  2  select c1, c2, sum(c2) over (order by c1) s from data
  3  /
C         C2          S
- ---------- ----------
a       1000       1000
b       2000       3000
c       3000       6000
d       4000      10000

4 rows selected.

Regards
Michel
Re: problem in SUM [message #272010 is a reply to message #272008] Wed, 03 October 2007 11:35 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
thanks a lot Michel
Re: problem in SUM [message #272012 is a reply to message #272010] Wed, 03 October 2007 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't thank me unless you understand the query.

Regards
Michel
Re: problem in SUM [message #272065 is a reply to message #272000] Wed, 03 October 2007 22:35 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
If you do not understand that query then read the following document

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions163.htm#i89126
Re: problem in SUM [message #272197 is a reply to message #272065] Thu, 04 October 2007 03:54 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
thanks..

SELECT manager_id, last_name, salary,
SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees;

my query is worked...

but i am not understand

OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING)


please explain this line how it is work...

thanks and regards,
Anamika
Re: problem in SUM [message #272201 is a reply to message #272197] Thu, 04 October 2007 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll be wanting to read the previous post from muzahidul islam, and to follow the link he gives to the SUM function, which has links to Analytic Functions as well (which is what the ORDER BY bit is).
Re: problem in SUM [message #272202 is a reply to message #272201] Thu, 04 October 2007 03:59 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
actually i am not understand this line

RANGE UNBOUNDED PRECEDING

what is the meaning of this line?
Re: problem in SUM [message #272209 is a reply to message #272202] Thu, 04 October 2007 04:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read the manual
Use Google
Explain what you read and which part you don't understand
Re: problem in SUM [message #272211 is a reply to message #272000] Thu, 04 October 2007 04:30 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
RANGE UNBOUNDED PRECEDING include all the preceding records in the current partition in the calculation for the current row
Re: problem in SUM [message #272214 is a reply to message #272211] Thu, 04 October 2007 04:39 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again spoon feeding someone is not helping him.
All those who posted before you know the answer. Please respect what they are trying to do.

Regards
Michel
Previous Topic: Problems with Timestamp
Next Topic: Password Encryption
Goto Forum:
  


Current Time: Fri Dec 09 01:48:33 CST 2016

Total time taken to generate the page: 0.09809 seconds