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 5, 2013 2:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:37 AM
Points: 57, Visits: 115
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 5, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
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 5, 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 @ 4:08 PM
Points: 20,859, Visits: 32,881
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 9, 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: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
You two are freaky scary sometimes.. :D
Post #1440605
Posted Tuesday, April 9, 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 @ 4:08 PM
Points: 20,859, Visits: 32,881
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 9, 2013 6:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:44 PM
Points: 927, Visits: 5,898
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