title: Sql 2012 Simplify Query Union
body: <p>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.</p>
  
  <pre><code>    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
  </code></pre>