CODEWITHSUNDEEP
sqlsql-servergroup-byrelational-databaseconditional-statements
Thomas H
Thomas H

Reputation: 51

Select a gender dominated group from a pupulation where one gendergroup has lower average salary but higher jobPoints

I have two tables:
Table One: columns – GroupName and GroupJobPoints.
Table two has this columns of interest here – GroupName (fk_key) reference to Table One, Person_ID, Gender, Salary.

I want to see from my database:
1) Get GroupName who is dominated by one gender (=> 60 % of one gender) – GO On with this groups.
2) Compare this gender dominated groups GroupJobPoints. And if the difference in GroupJobPoints is in range 4 % - compare the male groups to the females group.
3) Compare the average salary of the male groups with the average salary to the female groups in this 4 % range. If the male group has higher group average salary compared to the female Group - Then I want to know that and have it displayed.

Example: 1) Group 1 has 250 males and 50 females in the group. Group 2 has 20 males and 300 females. Both group is dominated by on gender, GO ON 2) Group 1 has 2 000 points and Group 2 has 1 950 points. The difference is in range 4 % and one Group is dominated by male and one Group is dominated by female. GO ON. 3) Group 1 has average salary = 25 000 and Group 2 has average salary = 22 000. I want this displayed because the male Group has lower jobpoints but higher average salary and that is Little bit strange for me :-).

How I can get this by SQL-code. I use MS SQL-server Express 2012. I assume there will be some joins and where clauses and so on and the code will be in step by step in some respect but I don´t figure it out how I will do this. can someone please help me with is and I´m sure I will learn a lot by this.

Upvotes: 0

Views: 150

Answers (1)

bgoldst
bgoldst

Reputation: 35324

with t1 as (
    select
        one.GroupName,
        one.GroupJobPoints,
        (select cast(count(1) as float) from TableTwo where GroupName=one.GroupName and Gender='M')/(select cast(count(1) as float) from TableTwo where GroupName=one.GroupName) FracMale,
        (select avg(Salary) from TableTwo where GroupName=one.GroupName) AvgSalary
    from
        TableOne one
)
select
    m.GroupName,
    m.GroupJobPoints,
    m.AvgSalary,
    m.FracMale,
    f.GroupName,
    f.GroupJobPoints,
    f.AvgSalary,
    f.FracMale
from
    t1 m
    cross join t1 f
where
    m.FracMale>=0.60
    and f.FracMale<=0.40
    and abs(f.GroupJobPoints-m.GroupJobPoints)/m.GroupJobPoints<=0.04
    and m.AvgSalary>f.AvgSalary
;

Test data:

if object_id('TableTwo') is not null drop table TableTwo;
if object_id('TableOne') is not null drop table TableOne;
create table TableOne (GroupName varchar(32), GroupJobPoints float, primary key (GroupName) );
create table TableTwo (GroupName varchar(32) references TableOne(GroupName), Person_ID int, Gender char(1), Salary float, primary key (Person_ID) );

insert into TableOne (GroupName, GroupJobPoints ) values ('1',2000);
insert into TableOne (GroupName, GroupJobPoints ) values ('2',1950);

declare @PersonID int = 0;
declare @i int;

set @i = 0; while (@i < 250) begin set @PersonID=@PersonID+1; insert into TableTwo (GroupName, Person_ID, Gender, Salary ) values ('1',@PersonID,'M',25000); set @i=@i+1; end;
set @i = 0; while (@i < 50) begin set @PersonID=@PersonID+1; insert into TableTwo (GroupName, Person_ID, Gender, Salary ) values ('1',@PersonID,'F',25000); set @i=@i+1; end;

set @i = 0; while (@i < 20) begin set @PersonID=@PersonID+1; insert into TableTwo (GroupName, Person_ID, Gender, Salary ) values ('2',@PersonID,'M',22000); set @i=@i+1; end;
set @i = 0; while (@i < 300) begin set @PersonID=@PersonID+1; insert into TableTwo (GroupName, Person_ID, Gender, Salary ) values ('2',@PersonID,'F',22000); set @i=@i+1; end;

Output from running all of the above:

output

Upvotes: 1

Related Questions