Home » SQL & PL/SQL » SQL & PL/SQL » problem in SUM
problem in SUM Wed, 03 October 2007 11:00
 anamika_025 Messages: 81Registered: 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
 BlackSwan Messages: 25571Registered: 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
 anamika_025 Messages: 81Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 anamika_025 Messages: 81Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 muzahid Messages: 281Registered: September 2004 Location: Dhaka, Bangladesh Senior Member
If you do not understand that query then read the following document

Re: problem in SUM [message #272197 is a reply to message #272065] Thu, 04 October 2007 03:54
 anamika_025 Messages: 81Registered: 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
 JRowbottom Messages: 5933Registered: 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
 anamika_025 Messages: 81Registered: 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
 Frank Messages: 7880Registered: March 2000 Senior Member
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
 muzahid Messages: 281Registered: 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
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Threading in Pl/SQL Next Topic: deleting a column
Goto Forum:

Current Time: Sun Aug 20 14:11:54 CDT 2017

Total time taken to generate the page: 0.05014 seconds