Want to categorize the salaries of employee.

Question

Post reply

Want to categorize the salaries of employee.

kiran.vaichalkar

Ten Centuries

Points: 1056
More actions
April 5, 2013 at 2:36 pm

#272781

Hi Team,
I've Table People Where there are n no of employees involved.
The salaries of the employee are present in Column 'Salary'
So now, i wanted to count the no of Employees in the range of Salaries as below
1) >10000 & <30000
2) >30000 & <80000
3) >800000
Im trying the above logic as per the below code, can any one please help me understand this, why IM getting '0' for the counts even for no error in the query?
'Select Count(p2.SALARY)As Range1, Count(p3.SALARY)As Range2,Count(p4.SALARY)As Range3
From PEOPLE p1
JOIN PEOPLE p2
ON ((p1.pID = p2.pID) AND (p1.SALARY BETWEEN 10000 AND 20000))
JOIN PEOPLE p3
ON ((p1.pID = p3.pID) AND (p1.SALARY BETWEEN 20000 AND 80000))
JOIN PEOPLE p4
ON ((p1.pID = p4.pID) AND (p1.SALARY > 80000))'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think you can streamline it a bit by using a neat technique using SUM(CASE..)

SELECT

SUM(CASE WHEN SALARY BETWEEN 10000 AND 20000 THEN 1 ELSE 0 END) As Range1,

SUM(CASE WHEN SALARY BETWEEN 20001 AND 80000 THEN 1 ELSE 0 END) As Range2,

SUM(CASE WHEN SALARY > 80000 THEN 1 ELSE 0 END) As Range3

From PEOPLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

kiran.vaichalkar (4/5/2013)
Hi Team,
I've Table People Where there are n no of employees involved.
The salaries of the employee are present in Column 'Salary'
So now, i wanted to count the no of Employees in the range of Salaries as below
1) >10000 & <30000
2) >30000 & <80000
3) >800000
Im trying the above logic as per the below code, can any one please help me understand this, why IM getting '0' for the counts even for no error in the query?
'Select Count(p2.SALARY)As Range1, Count(p3.SALARY)As Range2,Count(p4.SALARY)As Range3
From PEOPLE p1
JOIN PEOPLE p2
ON ((p1.pID = p2.pID) AND (p1.SALARY BETWEEN 10000 AND 20000))
JOIN PEOPLE p3
ON ((p1.pID = p3.pID) AND (p1.SALARY BETWEEN 20000 AND 80000))
JOIN PEOPLE p4
ON ((p1.pID = p4.pID) AND (p1.SALARY > 80000))'

Try this:

select

sum(case when p.Salary >= 10000 and p.Salary < 20000 then 1 else 0 end) as Range1,

sum(case when p.Salary >= 20000 and p.Salary < 80000 then 1 else 0 end) as Range2,

sum(case when p.Salary >= 80000 then 1 else 0 end) as Range3

from

People p

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

Erin Ramsay

SSCrazy Eights

Points: 9728

April 9, 2013 at 4:43 pm

#1605059

You two are freaky scary sometimes.. 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Erin Ramsay (4/9/2013)
You two are freaky scary sometimes.. 😀

And why do you say that?? 😉

pietlinden SSC Guru Points: 63509 More actions · Answer 5

It's because they know how to use the Force, and we don't...

Silly Us!