Select Statements to calculate min and max

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statements to calculate min and max

Author

Topic

RX840
Starting Member

9 Posts

Posted - 2008-12-17 : 20:48:38

Looking how to write a query

I wish to calculate the maximum salary for exempt employees and the max salary for non-exempt employees. Here are my tables and thanks for your assistance with this, i am very new to sql and am learning everyday.

Here's what I tried which fails:

select max(salary) from Employee;
WHERE Employee.JobId in
(Select JobId from Job where Job.ExStatus = 'Non' + Job.ExStatus = 'Exempt')

Here are my tables:

CREATE TABLE Job
(
JobId [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY (JobId),
JobTitle VARCHAR(75) NULL,
JobDesc VARCHAR(75) NULL,
ExStatus VARCHAR(10) NULL,
EeoCl VARCHAR(25) NULL,
JobCo INT NULL,
);

CREATE TABLE Employee
(
EmpId [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY (EmpId),
UNIQUE (EmpId),
JobId INT NULL,
LName VARCHAR(25) NULL,
FName VARCHAR(25) NULL,
Address VARCHAR(75) NULL,
City VARCHAR(25) NULL,
State CHAR(2) NULL,
Zip VARCHAR(10) NULL,
TelNum CHAR(15) NULL,
HireDate SMALLDATETIME NULL,
Salary DECIMAL (10,2),
Gender CHAR(1) NULL,
Age VARCHAR(3) NULL,
FOREIGN KEY (JobId) references Job(JobId)
);

Thanks,
Rex

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-17 : 20:59:59

May be this:
Select max(case when J.Exstatus ='Exempt' then E.salary else 0 end)as MAXEXEMPT,
max(case when J.status = '[Non-exempt]' then E.salary else 0 end) as MAXNONEXEMPT
from Employee E inner join Job J
on E.Jobid = J.Jobid

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 23:05:40

select max(salary) from Employee;
WHERE Employee.JobId in
(Select JobId from Job where Job.ExStatus = 'Non' AND Job.ExStatus = 'Exempt')
U should not use '+' operator use AND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:23:28

quote:
Originally posted by bklr

select max(salary) from Employee;
WHERE Employee.JobId in
(Select JobId from Job where Job.ExStatus = 'Non' AND Job.ExStatus = 'Exempt')
U should not use '+' operator use AND

this wont work. what you're looking for in subquery is record with status of both Non and Exempt which never exist. probabaly you meant or rather than and

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 23:53:20

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

select max(salary) from Employee;
WHERE Employee.JobId in
(Select JobId from Job where Job.ExStatus = 'Non' AND Job.ExStatus = 'Exempt')
U should not use '+' operator use AND

this wont work. what you're looking for in subquery is record with status of both Non and Exempt which never exist. probabaly you meant or rather than and

select max(salary) from Employee;
WHERE Employee.JobId in
(Select JobId from Job where Job.ExStatus = 'Non' OR Job.ExStatus = 'Exempt')

Thanks visakh
Now it will work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:57:40

it will work. but you will get a single maximum value, not one for each exstatus.for that you need to use something like sodeeps which will provide you with crosstabbed result or use group by exstatus in which case you will get it as rows

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 05:32:28

quote:
Originally posted by visakh16

it will work. but you will get a single maximum value, not one for each exstatus.for that you need to use something like sodeeps which will provide you with crosstabbed result or use group by exstatus in which case you will get it as rows

k thanks for ur suggestion

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 06:49:39

select max(salary)as MaxSalary,'Excempt' from Employee
WHERE Employee.JobId in
(Select JobId from Job where ExStatus ='Exempt')
UNION
select max(salary)as MaxSalary,'Non-Excempt' from Employee
WHERE Employee.JobId in
(Select JobId from testJob where ExStatus ='non-Exempt')

RX840
Starting Member

9 Posts

Posted - 2008-12-24 : 15:29:07

thank you Dark for that info, which was very helpful in us coming up with the following solution.

Thanks everyone who posted - thanks!!

Here's the final code we used for our project:

Select 'Exempt' as ExStatus, Max(Salary) As Max
from Employee
Join Job on Employee.JobId = Job.JobId
Where Job.ExStatus = 'Non'
Union
Select 'Non' as ExStatus, Max(Salary) As Max
from Employee
Join Job on Employee.JobId = Job.JobId
Where Job.ExStatus = 'Exempt'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-25 : 10:24:08

quote:
Originally posted by RX840

thank you Dark for that info, which was very helpful in us coming up with the following solution.

Thanks everyone who posted - thanks!!

Here's the final code we used for our project:

Select 'Exempt' as ExStatus, Max(Salary) As Max
from Employee
Join Job on Employee.JobId = Job.JobId
Where Job.ExStatus = 'Non'
Union
Select 'Non' as ExStatus, Max(Salary) As Max
from Employee
Join Job on Employee.JobId = Job.JobId
Where Job.ExStatus = 'Exempt'

Still Confused!! How will you know which one is for 'Exempt' and for 'Non'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources