Sign in to follow this  
fosh

sql union behaviour

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Couldn't you do something like this instead?


select deptno,deptname,
(select avg(salary) from emp where sex='M' and workdept=deptno) as male_avg_salary,
(select avg(salary) from emp where sex='F' and workdept=deptno) as female_average_salary
from dept


Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this