Home » SQL & PL/SQL » SQL & PL/SQL » sql*plus
sql*plus [message #211034] Mon, 25 December 2006 10:34 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
hello all

i run a query to get dept-wise salary in sql*plus

and my result is -

SQL> select dept_no, job, client_no, emp, hiredate, sal from emp1
  2  group by (dept_no, job, client_no, emp, hiredate, sal);

   DEPT_NO JOB      CLIENT        EMP HIREDATE         SAL                      
---------- -------- ------ ---------- --------- ----------                      
        10 clerk    C00001       2000 12-DEC-01     165000                      
           manager  C00004        500 12-FEB-01     440000                      
           manager  C00011       3980 29-FEB-04     209000                      
           manager  C00017        300               390000                      
        20  supreme C00013        390               451838                      
           analyst  C00005       5000 12-JUN-00       4800                      
           analyst  C00007        700 12-SEP-02      16800                      
           clerk    C00009        200 01-JAN-06     360000                      
           manager  C00018        450               459902                      
           supreme  C00005        390               451838                      
           supreme  C00013        390               451838                      
           supreme  C00016        800               367900                      
        30 clerk    C00003       3500 12-FEB-03     193700                      
           clerk    C00012        580               104000                      
           manager  C00002        200 12-OCT-05                                 
           manager  C00014        300               780000                      
           supreme  C00015       4980               260000                      
        40 analyst  C00010        600 25-DEC-05     126000                      
           manager  C00006        500 12-JUN-04     280000                      
           manager  C00008        200 02-JUL-06      25200                      

20 rows selected.

SQL> spool off



now i want to display sum(sal) of job on each dept_no through sql*plus.

such as(example)-
10      manager         100
        manager          20
         -------        ---
         sum            120

        clerk           40
        ------          ---
         sum            40

20      manager         30
        manager         40
        ---------       ---
         sum             70

hope i can make u understand about my request.

with regards
yas

[Updated on: Mon, 25 December 2006 10:36]

Report message to a moderator

Re: sql*plus [message #211037 is a reply to message #211034] Mon, 25 December 2006 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this live example? Pay attention to BREAK and COMPUTE SQL*Plus commands:
SQL> break on deptno on job skip 1
SQL> compute sum of sal on job
SQL> select deptno, job, sal from emp order by 1, 2;

    DEPTNO JOB              SAL
---------- --------- ----------
        10 CLERK           1300
           ********* ----------
           sum             1300

           MANAGER         2450
           ********* ----------
           sum             2450

           PRESIDENT       5000
           ********* ----------
           sum             5000

        20 ANALYST         3000
                           3000
           ********* ----------
           sum             6000

           CLERK           1100
                            800
           ********* ----------
           sum             1900

           MANAGER         2975
           ********* ----------
           sum             2975

        30 CLERK            950
           ********* ----------
           sum              950

           MANAGER         2850
           ********* ----------
           sum             2850

           SALESMAN        1250
                           1250
                           1600
                           1500
           ********* ----------
           sum             5600


14 rows selected.

SQL>
Re: sql*plus [message #211045 is a reply to message #211034] Mon, 25 December 2006 19:17 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
thanx littlefoot

one more request to u.
if i want to display on sql, can i ?
give some hints, so that i can do.

with regards
yas
Re: sql*plus [message #211075 is a reply to message #211045] Tue, 26 December 2006 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I don't understand the question ... what would you like "to display on SQL"?
Re: sql*plus [message #211082 is a reply to message #211034] Tue, 26 December 2006 03:01 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
hello littlefoot.

i m sorry. i unable to make u understand about my request.

i want to get the following result through sql-


   DEPTNO JOB              SAL
---------- --------- ----------
        10 CLERK           1300
           ********* ----------
           sum             1300

           MANAGER         2450
           ********* ----------
           sum             2450

           PRESIDENT       5000
           ********* ----------
           sum             5000

        20 ANALYST         3000
                           3000
           ********* ----------
           sum             6000

           CLERK           1100
                            800
           ********* ----------
           sum             1900

           MANAGER         2975
           ********* ----------
           sum             2975

        30 CLERK            950
           ********* ----------
           sum              950

           MANAGER         2850
           ********* ----------
           sum             2850

           SALESMAN        1250
                           1250
                           1600
                           1500
           ********* ----------
           sum             5600


14 rows selected.


can we do it without the help of sql*plus?

thanxx

regards
yas
Re: sql*plus [message #211084 is a reply to message #211082] Tue, 26 December 2006 03:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What purpose would such a sql have? You cannot use the outcome of it, because you don't know the contents of the columns.
SQL is NOT a markup language!
Previous Topic: get a text according to a delimeter
Next Topic: delete case sensetive
Goto Forum:
  


Current Time: Sun Dec 11 06:21:28 CST 2016

Total time taken to generate the page: 0.28132 seconds