Simple SQL Query help

[bingwalker]

I have 2 tables, one for the employees name, salary,jobtitle and another table for the jobtitle description like exempt, min salary, etc...

I need a simple query statement for:

Calculate the avg salary for all exempt employees and non-exempt employees with the same statement.

Help!!

 

[osjohnm]

hi

try something like this

select avg(a.salary) as Exempt
from table a, table b
where a.col = b.col
and b.exempt = 'Yes'
union
select avg(a.salary) as Non-Exempt
from table a, table b
where a.col = b.col
and b.exempt = 'No'

If you want an average over both exempt and non-exempt and you want one amount then try the following:

select avg(a.salary) as Exempt
from table a, table b
where a.col = b.col
and b.exempt in ('Yes', No)

Hope this helps

John

 

[bingwalker]

Thanks!

Here is what I have. I'm not sure what to put in the a.col, b.col part

select avg(emp.salary) as Exempt
from emp, job
where a.col = b.col
and job.exempt = 'Yes'
union
select avg(emp.salary) as Non-Exempt
from emp, job
where a.col = b.col
and job.exempt = 'No'

 

[osjohnm]

Hi

Sorry I should have been clearer. ColA and ColB should be replace with the fields that you use to JOIN your employees table and job description table.

John

 

[MeanGreen]

Here is another alternative to your request:

select case when b.exempt = 'Yes' then 'EXEMPT'
when b.exempt = 'No' then 'Non-Exempt' end as EMPLOYEE,
avg(a.salary) as SALARY
from table a join table b
where a.jobtitle = b.jobtitle
group by case when b.exempt = 'Yes' then 'EXEMPT'
when b.exempt = 'No' then 'Non-Exempt' end

Hope this helps.