CODEWITHSUNDEEP
sqlsql-server
chaixdev
chaixdev

Reputation: 122

Selecting rows based on conditional GROUP BY

Suppose a table 'employees':

table employees

[id|name|department|sex|salary]

Is there a way to display all departments where the minimum male employee salary is equal to or greater than the maximum female employee salary?

This is an exercise that i have a tough time getting my head around. I know there will be GROUP BY clauses involved, but i dont really have a grasp on how they work yet.

Answers preferred in MSSQL syntax, if it matters at all.

Upvotes: 1

Views: 188

Answers (2)

Eralper
Eralper

Reputation: 6612

The following statement can be used as well

select 
    a.*, b.minM
from 
(
select
    department, max(salary) maxF 
from employees
where sex = 'F'
group by department
) A,
(
select
    department, min(salary) minM
from employees
where sex = 'M'
group by department
) B
where a.Department = B.department and maxF <= minM

Upvotes: 1

Nicholai
Nicholai

Reputation: 818

I'm not by Mgmt Studio to verify it, but I think this will give you what you need:

WITH cte AS (
SELECT
  department,
  min(case when sex = 'M' then salary else 10000000000) as MinMaleSalary,
  max(case when sex = 'F' then salary else 0) as MaxFemaleSalary
FROM
  Employees
GROUP BY department
)

SELECT *
FROM cte
WHERE MinMaleSalary >= MaxFemaleSalary;

If this doesn't work, you can use a second cte to get Min Male and Max Female separately before comparing them.

Upvotes: 1

Related Questions