-
Hi All,
ok, here's the scenario:
Each employee has a type (1,2,3) and I want to select salary for emp's who have type in (1,2) each, so I wrote:
select dept, decode(type, 1, sum(salary*.4)) sal1,
decode(type, 2, sum(salary*.5)) sal2
from emp
where type in (1,2);
but i get 2 rows returned:
dept sal1 sal2
------ ----- -----
101 555
101 666
any help rendered is appreciated...asap please.....
thanx
oh, and if i can get out of it sal1+sal2 at the same time is appreciated!
-
Hi,
something like that?
Code:
SELECT dept, sal1, sal2, sal1+sal2 as sal12 FROM
(
select
dept,
0.4*sum(decode(type, 1, salary, 0)) sal1,
0.5*sum(decode(type, 2, salary, 0)) sal2
from emp
where type in (1,2)
GROUP BY dept
)
-
Or in case you can't use inline view as in ales's solution, you can use this simple query:
select deptno,
sum(decode(type, 1, sal*0.4, 0)) sal1,
sum(decode(type, 2, sal*0.5, 0)) sal2,
sum(decode(type, 1, sal*0.4, 2, sal*0.5, 0)) sal12
from emp
where type in (1,2)
group by deptno;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Of course, you would never actually want to bury those constants in a SQL statement. It would be much better to have the .4 and .5 stored in the EmpType_T table:
Code:
EmpType_T:
EmpType_ID SalMod
1 0.4
2 0.5
3 1
Then, the SQL would be:
Code:
SELECT
E.DeptNo,
SUM(DECODE(TYPE, 1, E.SAL * ET.SalMod, 0)) SAL1,
SUM(DECODE(TYPE, 2, E.SAL * ET.SalMod, 0)) SAL2,
SUM(DECODE(TYPE, 1, E.SAL * ET.SalMod, 2, E.SAL * SalMod, 0)) SAL12
FROM
Emp E ,
EmpType_T ET
WHERE
E.EmpType IN (1,2) AND
ET.EmpType = E.EmpType AND
ET.EmpType IN (1,2)
GROUP BY
E.DeptNo;
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|