Jump to content
  • Advertisement
Sign in to follow this  
fosh

sql union behaviour

This topic is 4694 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
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
HAHAHAHHA
oh god i cant believe i didnt realise i could just do it that way !
cheers mate

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!