Home » SQL & PL/SQL » SQL & PL/SQL » total
total [message #299776] Wed, 13 February 2008 03:05 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how can we get this from a query
sample query
select cl.empno,cl.ename,cl.job,sal,cl.deptno
(select sum(sal),deptno
from emp where deptno=10
group by deptno)dte,
(select sum(sal),deptno
from emp where deptno=20
group by deptno)dtw,
(select sum(sal),deptno
from emp where deptno=30
group by deptno)dth
from 
(select empno,ename,job,sal,deptno from emp)cl


job	dept10	dept20	dept30	sal
CLERK	1500	555	850	2905
MANAGER	 6000	750	550	7300
PRESIDENT 7500	1500	1500	10500
ANALYST	 5000	2500	250	7750
GrandTotal 20000 5305	3150   28455
Re: total [message #299778 is a reply to message #299776] Wed, 13 February 2008 03:10 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
select cl.job,
(select sum(sal)
from emp where deptno=10
group by deptno)dept10,
(select sum(sal)
from emp where deptno=20
group by deptno)dept20,
(select sum(sal)
from emp where deptno=30
group by deptno)dept30
from
(select empno,ename,job,sal,deptno from emp)cl
group by cl.job

hi
i need to get
GrandTotal 20000 5305 3150 28455
Re: total [message #299781 is a reply to message #299778] Wed, 13 February 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you format your second post?

COMPUTE

Regards
Michel
Re: total [message #299793 is a reply to message #299781] Wed, 13 February 2008 03:55 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
my query retruns sum of sal for the dept which is not correct
i need for each dept each job..
i need to get oth when if there is no value
and one more column which will display total job vice
cannot use compute due application genration report

job	dept10	dept20	dept30	TSJV
CLERK	1500	555	850	2905
MANAGER	6000	750	550	7300
PRESIDENT 7500	1500	1500	10500
ANALYST	5000	OTH	250	5250
SALESMAN OTH	OTH	450	450
Grand Total 20000 2805	3600	26405
Re: total [message #299794 is a reply to message #299793] Wed, 13 February 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As your query does not follow the guidelines, I can't read it.
I remind you:
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, align the columns.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 13 February 2008 04:03]

Report message to a moderator

Re: total [message #299807 is a reply to message #299776] Wed, 13 February 2008 04:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try without Group query ,
Hard-cord "Grant total"

Thumbs Up
Rajuvan.
Re: total [message #299809 is a reply to message #299794] Wed, 13 February 2008 04:50 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
select cl.job,
(select sum(sal)
from emp where deptno=10
group by deptno)dept10,
(select sum(sal)
from emp where deptno=20
group by deptno)dept20,
(select sum(sal)
from emp where deptno=30
group by deptno)dept30
from 
(select empno,ename,job,sal,deptno from emp)cl
group by cl.job


is this ok ...

job                  dept10	dept20	dept30  sal
  CLERK	            oth 	555	850	1400
  MANAGER	    6000	750	550	7300
  PRESIDENT         oth 	oth	1500    1500 
  ANALYST	    5000	2500	oth	7500
  GrandTotal        14000       3805	2900	19205
Re: total [message #299811 is a reply to message #299776] Wed, 13 February 2008 04:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What is yit expected result

job	dept10	dept20	dept30	sal
CLERK	1500	555	850	2905
MANAGER	 6000	750	550	7300
PRESIDENT 7500	1500	1500	10500
ANALYST	 5000	2500	250	7750
GrandTotal 20000 5305	3150   28455


Or

GrandTotal 20000 5305 3150 28455 


?

Thumbs Up
Rajuvan.

[Updated on: Wed, 13 February 2008 04:53]

Report message to a moderator

Re: total [message #299812 is a reply to message #299809] Wed, 13 February 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I execute your query (which by the way is not formatted) I get:
SQL> select cl.job,
  2  (select sum(sal)
  3  from emp where deptno=10
  4  group by deptno)dept10,
  5  (select sum(sal)
  6  from emp where deptno=20
  7  group by deptno)dept20,
  8  (select sum(sal)
  9  from emp where deptno=30
 10  group by deptno)dept30
 11  from 
 12  (select empno,ename,job,sal,deptno from emp)cl
 13  group by cl.job;
JOB           DEPT10     DEPT20     DEPT30
--------- ---------- ---------- ----------
CLERK           8750      10875       9400
SALESMAN        8750      10875       9400
PRESIDENT       8750      10875       9400
MANAGER         8750      10875       9400
ANALYST         8750      10875       9400

5 rows selected.

Regards
Michel
Re: total [message #299823 is a reply to message #299776] Wed, 13 February 2008 05:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Seems you are looking for ROLLUP extension of GROUP BY clause.

Just a demonstration:
SQL> SELECT DECODE( GROUPING_ID(job), 1, 'Grand Total', job ) job, COUNT(*)
  2  FROM employees
  3  GROUP BY ROLLUP(job);

JOB               COUNT(*)
--------------- ----------
ANALYST                  2
CLERK                    4
MANAGER                  3
PRESIDENT                1
SALESMAN                 4
Grand Total             14

6 rows selected.
Re: total [message #299827 is a reply to message #299823] Wed, 13 February 2008 05:51 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
select ename,job,
(select sum(sal) from emp scr
where deptno=10
group by deptno,job)d10,
(select sum(sal) from emp scr
where deptno=20
group by deptno,job)d20,
(select sum(sal) from emp scr
where deptno=30
group by deptno,job)d30 from emp

ORA-01427: single-row subquery returns more than one row
Re: total [message #299836 is a reply to message #299776] Wed, 13 February 2008 06:13 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Try this one.

compute sum of dept10 on job
compute sum of dept20 on job
compute sum of dept30 on job
compute sum of sal on job


break on job


Thanks

Mano
Re: total [message #299843 is a reply to message #299776] Wed, 13 February 2008 06:21 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Surprisingly, the query for achieving this goal can be found on these pages: http://www.orafaq.com/wiki/PIVOT
For totals, read my previous post.
Previous Topic: Numbers from 1 to 10
Next Topic: Oracle 9 i
Goto Forum:
  


Current Time: Mon Dec 05 06:57:48 CST 2016

Total time taken to generate the page: 0.17662 seconds