Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want to categorize the salaries of employee. Expand / Collapse
Author
Message
Posted Friday, April 05, 2013 2:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 15, 2014 2:44 AM
Points: 54, Visits: 97
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))'
Post #1439486
Posted Friday, April 05, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 12,741, Visits: 31,052
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439488
Posted Friday, April 05, 2013 2:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439489
Posted Tuesday, April 09, 2013 4:43 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:55 AM
Points: 532, Visits: 984
You two are freaky scary sometimes.. :D
Post #1440605
Posted Tuesday, April 09, 2013 4:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
Erin Ramsay (4/9/2013)
You two are freaky scary sometimes.. :D


And why do you say that??



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1440606
Posted Tuesday, April 09, 2013 6:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 567, Visits: 3,723
It's because they know how to use the Force, and we don't...

Silly Us!
Post #1440617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse