Home » SQL & PL/SQL » SQL & PL/SQL » case statment doubt
case statment doubt [message #246815] Fri, 22 June 2007 01:38 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

I am trying to display the sum of employee salary according to department range.

select dept_rang ,sum(sal) from (select case when deptno between 10 and 30 then '10-30'
when deptno between 40 and 80 then '40-80' end dept_rang,sal from emp) group by dept_rang 


my output is as-:
10-30,29036
,20


i am not getting why this query is returning the sum of employee salary which department number is null ..


table data is as-:
sal,dept
800,20
1600,30
1250,30
2975,20
1250,30
2850,30
2450,10
3000,20
5000,10
1500,30
1100,20
950,30
3000,20
1300,10
11,20
10,
10,


--Yash
Re: case statment doubt [message #246818 is a reply to message #246815] Fri, 22 June 2007 01:44 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Do u really get this output?. I don't think so? Can u post what u r actually getting?
Re: case statment doubt [message #246821 is a reply to message #246815] Fri, 22 June 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When formatted you get:
SQL> select dept_rang ,sum(sal) from (select case when deptno between 10 and 30 then '10-30'
  2  when deptno between 40 and 80 then '40-80' end dept_rang,sal from emp) group by dept_rang ;

DEPT_   SUM(SAL)
----- ----------
10-30      29036
              20

2 rows selected.

And you get the sum of salaries in deptno NULL because when something does not fit in your intervalles then it is in dept_rang NULL.

Regards
Michel

[Updated on: Fri, 22 June 2007 01:51]

Report message to a moderator

Re: case statment doubt [message #246827 is a reply to message #246821] Fri, 22 June 2007 02:10 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx michel!! i was thinking in wrong direction ..i was thinking why i am getting the sum of salary which dept is null ...

actually null range is the sum of all thoese dept salary which are not cmg in the intervalles.


--Yash
Re: case statment doubt [message #246847 is a reply to message #246815] Fri, 22 June 2007 04:03 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Do u really get this output?. I don't think so? Can u post what u r actually getting?
Re: case statment doubt [message #246848 is a reply to message #246847] Fri, 22 June 2007 04:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you think that isn't what he's getting?

I took 2 minutes out and produced this:
drop table emp_test;

create table emp_test (sal number, deptno number);

insert into emp_Test values (800,20);
insert into emp_Test values (1600,30);
insert into emp_Test values (1250,30);
insert into emp_Test values (2975,20);
insert into emp_Test values (2850,30);
insert into emp_Test values (2450,10);
insert into emp_Test values (3000,20);
insert into emp_Test values (5000,10);
insert into emp_Test values (1500,30);
insert into emp_Test values (1100,20);
insert into emp_Test values (950,30);
insert into emp_Test values (3000,20);
insert into emp_Test values (1300,10);
insert into emp_Test values (11,20);
insert into emp_Test values (10,null);
insert into emp_Test values (10,null);

select dept_rang ,sum(sal) from (select case when deptno between 10 and 30 then '10-30'
when deptno between 40 and 80 then '40-80' end dept_rang,sal from emp_test) group by dept_rang;

DEPT_RANG SUM(SAL)               
--------- ---------------------- 
10-30     27786                  
          20                     

Now, granted the op has changed the format by having commas seperate the columns, but you can do that with the COLSEP command.
So I think this really is what the OP is getting.
Previous Topic: small logical query doubt
Next Topic: spool into file
Goto Forum:
  


Current Time: Thu Dec 08 10:45:51 CST 2016

Total time taken to generate the page: 0.14223 seconds