Home » SQL & PL/SQL » SQL & PL/SQL » Query about SQl (Oracle 9i)
Query about SQl [message #345555] Thu, 04 September 2008 03:23 Go to next message
khadeer786
Messages: 1
Registered: February 2008
Junior Member
Hi,
Query is this,that to add 1st row sal & 2 row sal and show this result in calculated sum column.....

EMPNO ENAME SAL calculated_sum (Expected Result)
----- ----- --- -------------------------------
7876 ADAMS 1100 1100
7499 ALLEN 1600 2700 (1100+1600)
7698 BLAKE 2850 5550 (2700+2850).....
7782 CLARK 2450
7902 FORD 3000
7900 JAMES 950
7566 JONES 2975
7839 KING 5000
7654 MARTIN 1250
7934 MILLER 1300
7788 SCOTT 3000
7369 SMITH 1800
7844 TURNER 1500
7521 WARD 1250


Thanks in Advance
Khadeer

[Updated on: Thu, 04 September 2008 03:30]

Report message to a moderator

Re: Query about SQl [message #345556 is a reply to message #345555] Thu, 04 September 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SUM in its analytical form.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Query about SQl [message #345575 is a reply to message #345555] Thu, 04 September 2008 04:24 Go to previous messageGo to next message
kc1982
Messages: 13
Registered: October 2006
Junior Member
You may check this guide for reference

http://www.oracle.com/technology/oramag/code/tips2005/062005.html
icon14.gif  Re: Query about SQl [message #345787 is a reply to message #345555] Thu, 04 September 2008 15:24 Go to previous messageGo to next message
sumanta
Messages: 1
Registered: September 2008
Junior Member
try this--

select empno,sal,sal + lag(sal,1,0) over ( partition by dept ORDER BY empno ) cal_sal
from
( select empno,empname,sal, 10 as dept from temp_1) order by empno




Re: Query about SQl [message #345838 is a reply to message #345787] Fri, 05 September 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not work.

Also, please read the forum guide as well.

Regards
Michel
Re: Query about SQl [message #345856 is a reply to message #345838] Fri, 05 September 2008 02:18 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi,
hope this Query will work
select empno,
       ename,
       sal,
       sum(sal) over(partition by null order by sal, empno)sum
  from emp
Re: Query about SQl [message #345879 is a reply to message #345856] Fri, 05 September 2008 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ramya29p

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Your post doesn't help OP, will you provide him all the queries he will need in the future?
Let him learn!

By the way, "partition by null" is useless, and can you explain your "order by" clause?

Regards
Michel
Re: Query about SQl [message #345998 is a reply to message #345555] Fri, 05 September 2008 09:11 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Good work ramya.

Hope without the partition here, it will meet his requirements.

Please let me explain the order by clause.

ORDER BY (empno,sal) - Create the running total of the SAL column after the data was sorted by the empno and then sal.

This will work current rows and previous rows.

and here,

ORDER BY (empno,sal) and ORDER BY (sal,empno) - The sortered order will be different in the resultset.

SELECT 
  empno,
  ename,
  sal,
  SUM(sal) OVER (ORDER BY empno,sal) calculated_amount
FROM 
  scott.emp b


Re: Query about SQl [message #346008 is a reply to message #345998] Fri, 05 September 2008 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me explain the order by clause.

ORDER BY (empno,sal) - Create the running total of the SAL column after the data was sorted by the empno and then sal.

You don't explain it you just paraphrase it.
Why "sorting by the empno and then sal"?

Quote:
ORDER BY (empno,sal) and ORDER BY (sal,empno) - The sortered order will be different in the resultset.

And in the result!

Regards
Michel
Re: Query about SQl [message #346109 is a reply to message #345555] Sat, 06 September 2008 01:15 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Thanks for your comments.

ORDER BY (empno,sal) - Here sal is dont need.

ORDER BY (empno) - the query will take the current empno row and prior row to calculate the calculated_amount.

Re: Query about SQl [message #346110 is a reply to message #346109] Sat, 06 September 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But why not order by ename or by (deptno,hiredate,job)?

Regards
Michel
Re: Query about SQl [message #346113 is a reply to message #345555] Sat, 06 September 2008 01:46 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Quote:

But why not order by ename or by (deptno,hiredate,job)?



As per requirement here,

You can use the ename, if you have unique ename in your table.

You can use the hiredate, if you have unique hiredate in your table.

If you use the deptno, then you will get sum(sal) for the group by deptno wise.

If you use the deptno, then you will get the sum(sal) for the group by job wise.

If any clarification kindly revert.


Re: Query about SQl [message #346117 is a reply to message #346113] Sat, 06 September 2008 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
spmano1983 wrote on Sat, 06 September 2008 08:46
If you use the deptno, then you will get sum(sal) for the group by deptno wise.

If you use the deptno, then you will get the sum(sal) for the group by job wise.


Nonsense. You specify the way the records are ordered, in order to know which records to add. (record 2 has the sum of record 1 and 2; record n will have the sum of record 1... record n)
You have no grouping (partition by is omitted), so there is no summing per any group.

Also, if you add an ordering clause to your sum(), make sure to add the same order by to the main query.
Re: Query about SQl [message #346132 is a reply to message #345555] Sat, 06 September 2008 07:33 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Here i tried to mention,

calculated_amount will be same for the same department.
Re: Query about SQl [message #346136 is a reply to message #345555] Sat, 06 September 2008 07:42 Go to previous messageGo to next message
sanity08
Messages: 1
Registered: September 2008
Junior Member
hi,

you can use 'over order by sal desc' for the required result.

san

[EDITED by LF: removed huge font size]

[Updated on: Sat, 06 September 2008 07:45] by Moderator

Report message to a moderator

Re: Query about SQl [message #346145 is a reply to message #346136] Sat, 06 September 2008 09:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See output, sal in NOT in descending order.

Regards
Michel
Previous Topic: Select the Multi Rows of Column in single Row.
Next Topic: DDL statement in Procedure
Goto Forum:
  


Current Time: Sat Dec 03 05:40:32 CST 2016

Total time taken to generate the page: 0.10931 seconds