CODEWITHSUNDEEP
sqlleft-joinunion
bgh12g
bgh12g

Reputation: 3

Sql 2012 Simplify Query Union

I recently took a new job and I am trying to simplify some older queries left around and for the life of me I cannot figure out how to get this down into two queries using union. There's got to be a way but I cant simplify more than what I have.

    select  (
    select avg (Employee.salary)
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='0'
    group by Employee.Exempt_Non_Exempt_Status
    ) as AverageSalary, 'Non-Exempt' as Status
    ,       (
    select MIN (Employee.salary)
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='0'
    group by Employee.Exempt_Non_Exempt_Status
    ) as MinimumSalary, 'Non-Exempt' as Status
    ,       (
    select MAX (Employee.salary)
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='0'
    group by Employee.Exempt_Non_Exempt_Status
    ) as MaximumSalary, 'Non_Exempt' as Status
 UNION
    select  (
    select avg (Employee.salary)
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='1'
    group by Employee.Exempt_Non_Exempt_Status
    ) as AverageSalary, 'Exempt' as Status
    ,       (
    select MIN (Employee.salary)
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='1'
    group by Employee.Exempt_Non_Exempt_Status
    ) as MinimumSalary, 'Exempt' as Status
    ,       (
    select MAX (Employee.salary) 
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='1'
    group by Employee.Exempt_Non_Exempt_Status
    )as MaximumSalary, 'Exempt' as Status

Upvotes: 0

Views: 42

Answers (3)

bgh12g
bgh12g

Reputation: 3

Thanks to everyone for the fast response. here is the code I went with. I was able to get the same results with all the code provided and want to thank you all.

SELECT Min(salary) as 'Minimum Salary', Max(salary) as 'Maximum Salary',Avg(salary) as 'Average Salary', 'Non-Exempt' AS Status 
FROM   (SELECT employee.salary 
    FROM   employee 
    WHERE  employee.exempt_non_exempt_status = '0') thingy1
UNION 
SELECT Min(salary) as 'Minimum Salary', Max(salary) as 'Maximum Salary', Avg(salary) as 'Average Salary', 'Exempt' AS Status 
FROM   (SELECT employee.salary 
    FROM   employee 
    WHERE  employee.exempt_non_exempt_status = '1') thingy2

Upvotes: 0

Kritner
Kritner

Reputation: 13765

You should be able to do something like this:

select min(salary), max(salary), avg(salary), 'Non-Exempt' as otherThingy
from (
    select Employee.salary
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='0'
) thingy
UNION
select min(salary), max(salary), avg(salary), 'Exempt' as otherThingy
from (
    select Employee.salary
    from Employee left join Job_title
    on Employee.job_title = Job_title.Job_title
    where Employee.Exempt_Non_Exempt_Status='1'
) thingy

or to get really fancy you could do it all in one go most likely:

http://sqlfiddle.com/#!6/8b437/1

select 
    min(salary), 
    max(salary), 
    avg(salary), 
    case when Exempt_Non_Exempt_Status = 0 
        then 'Non-Exempt' 
        else 'Exempt' 
    end as exemptStatus
from (
    select Employee.salary, Exempt_Non_Exempt_Status
    from Employee
) thingy
group by Exempt_Non_Exempt_Status

Upvotes: 0

Jason Geiger
Jason Geiger

Reputation: 2112

Since you are only selecting 1 field in each of the subqueries, you don't need a "Group By". Also, depending on the the data, you might not need to link in the Job_title Table.

If you need the Job_title table then...

SELECT Min(salary), Max(salary), Avg(salary), 'Non-Exempt' AS Status 
FROM   (SELECT employee.salary 
        FROM   employee 
                LEFT JOIN job_title  ON employee.job_title = job_title.job_title 
        WHERE  employee.exempt_non_exempt_status = '0') 
UNION 
SELECT Min(salary), Max(salary), Avg(salary), 'Exempt' AS Status 
FROM   (SELECT employee.salary 
        FROM   employee 
               LEFT JOIN job_title 
                      ON employee.job_title = job_title.job_title 
        WHERE  employee.exempt_non_exempt_status = '1')

If you don't need to link in the Job_title (since you're not using it anywhere) then...

SELECT Min(salary), Max(salary), Avg(salary), 'Non-Exempt' AS Status 
FROM   (SELECT employee.salary 
        FROM   employee 
        WHERE  employee.exempt_non_exempt_status = '0') 
UNION 
SELECT Min(salary), Max(salary), Avg(salary), 'Exempt' AS Status 
FROM   (SELECT employee.salary 
        FROM   employee 
        WHERE  employee.exempt_non_exempt_status = '1')

Upvotes: 1

Related Questions