OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » quries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

quries [message #2298]

Wed, 03 July 2002 21:45

chandra sekhar.s
Messages: 13
Registered: July 2002

Junior Member

Please solve the following queries :

1) Write a query to display the below given output
(Query based on EMP table)

Total 1980 1981 1982 1983
----- ---- ---- ---- ----
14 1 10 2 1

2) Write a query to display the below given output
(Query based on EMP table)

Job Dept 10 Dept 20 Dept 30 Total
--- ------- ------- ------- -----
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600

Report message to a moderator

Re: queries [message #2300 is a reply to message #2298]

Wed, 03 July 2002 22:16

J. Okester
Messages: 1
Registered: July 2002

Junior Member

select 14 "Total", 1 "1980", 10 "1981", 2 "1982", 1 "1983"
  from emp
 where rownum = 1;

select decode(rownum, 1, 'ANALYST', 2, 'CLERK', 3, 'MANAGER', 4, 'PRESIDENT', 5, 'SALESMAN') "Job",
decode(rownum, 1, null, 2, 1300, 3, 2450, 4, null, 5, null) "Dept 10",
decode(rownum, 1, null, 2, 1900, 3, 2975, 4, null, 5, null) "Dept 20",
decode(rownum, 1, 6000, 2, 950, 3, 2850, 4, 5000, 5, 5600) "Dept 30",
decode(rownum, 1, 6000, 2, 4150, 3, 8275, 4, 5000, 5, 5600) "Total"
  from emp
 where rownum <= 5;

Report message to a moderator

Re: quries [message #2305 is a reply to message #2298]

Thu, 04 July 2002 01:20

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> ed
Wrote file afiedt.buf

  1  SELECT  *
  2     FROM  (SELECT job,
  3        sum(decode(deptno,10,sal)) DEPT10,
  4        sum(decode(deptno,20,sal)) DEPT20,
  5        sum(decode(deptno,30,sal)) DEPT30,
  6        sum(decode(deptno,40,sal)) DEPT40,
  7        sum(sal) total
  8             from emp
  9     GROUP BY job)
 10* ORDER BY 1
SQL> /

JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600

just TO add some fancy you can even get a cross-tab report

SQL> break on report
SQL> compute sum of dept10 dept20 dept30 dept40 total on report
SQL>  SELECT  *
  2      FROM  (SELECT job,
  3         sum(decode(deptno,10,sal)) DEPT10,
  4         sum(decode(deptno,20,sal)) DEPT20,
  5         sum(decode(deptno,30,sal)) DEPT30,
  6         sum(decode(deptno,40,sal)) DEPT40,
  7         sum(sal) total
  8              from emp
  9      GROUP BY job)
 10   ORDER BY 1
 11  /

JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600
          ---------- ---------- ---------- ---------- ----------
sum             8750      10875       9400                 29025

Report message to a moderator

Previous Topic:	Rows into Columns
Next Topic:	Can anyone tell me about the usage of 'DECODE' ??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:26:40 CDT 2024