Hi everyone,
Im having trouble getting an sql query going. Before you ask, this isnt homework, im revising for a midterm exam and i cant work this out :P
There are 2 tables used in the query:
EMP
---
Workdept
Sex
Firstname
Lastname
DEPT
----
deptno (fk to workdept)
deptname
I am trying to write a query that shows a row for each department, like this:
deptno deptname male_avg_salary female_average_salary
------ -------- --------------- ---------------------
so far, i have this:
select * from
(
(select 'F' as g,avg(salary) as woman_average, workdept, deptname
from (select e.salary, e.workdept, d.deptname from emp e, dept d
where Sex='F'
and e.workdept=d.deptno)
group by workdept,deptname)
UNION
(select 'M' as g,avg(salary) as man_average, workdept, deptname
from (select e.salary, e.workdept, d.deptname from emp e, dept d
where Sex='M'
and e.workdept=d.deptno)
group by workdept,deptname)
) ;
I cant really tell why, but this union doesnt seem to be working properly. whichever gender is being queried in the second half doesnt make it into the output of the overall command.
I also cant really see how to merge the output so that everything ends up on one line for each department, instead of 2.
I believe this will work, once the union is fixed, but it is rather dodgy and im sure there is a better way to do it:
select max(workdept) as dept,max(deptname)as deptname ,max(man_average) as Male_average from
(
select 'M' as g,avg(salary) as man_average, workdept, deptname
from (select e.salary, e.workdept, d.deptname from emp e, dept d
where Sex='M'
and e.workdept=d.deptno)
group by workdept,deptname
UNION
select 'F' as g,avg(salary) as woman_average, workdept, deptname
from (select e.salary, e.workdept, d.deptname from emp e, dept d
where Sex='F'
and e.workdept=d.deptno)
group by workdept,deptname
)
group by workdept,deptname ;
Any help would be really great, ive been stuck on this for a while now :(
If it makes any difference, im using oracle.
