| total [message #299776] |
Wed, 13 February 2008 03:05  |
oracle_coorgi
Messages: 188
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  |
oracle_coorgi
Messages: 188
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 #299793 is a reply to message #299781] |
Wed, 13 February 2008 03:55 |
oracle_coorgi
Messages: 188
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #299809 is a reply to message #299794] |
Wed, 13 February 2008 04:50 |
oracle_coorgi
Messages: 188
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 #299812 is a reply to message #299809] |
Wed, 13 February 2008 05:11 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
flyboy
Messages: 1903
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 |
oracle_coorgi
Messages: 188
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
