Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Want to categorize the salaries of employee. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, April 05, 2013 2:36 PM
 Valued Member Group: General Forum Members Last Login: Monday, October 07, 2013 10:50 AM Points: 51, Visits: 70
 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 below1) >10000 & <300002) >30000 & <800003) >800000Im 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 Range3From PEOPLE p1JOIN PEOPLE p2 ON ((p1.pID = p2.pID) AND (p1.SALARY BETWEEN 10000 AND 20000)) JOIN PEOPLE p3ON ((p1.pID = p3.pID) AND (p1.SALARY BETWEEN 20000 AND 80000))JOIN PEOPLE p4ON ((p1.pID = p4.pID) AND (p1.SALARY > 80000))'
Post #1439486
 Posted Friday, April 05, 2013 2:40 PM
 SSChampion Group: General Forum Members Last Login: Today @ 1:42 PM Points: 12,228, Visits: 29,344
 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 Range3From 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 Group: General Forum Members Last Login: Today @ 10:59 AM Points: 22,083, Visits: 28,983
 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 below1) >10000 & <300002) >30000 & <800003) >800000Im 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 Range3From PEOPLE p1JOIN PEOPLE p2 ON ((p1.pID = p2.pID) AND (p1.SALARY BETWEEN 10000 AND 20000)) JOIN PEOPLE p3ON ((p1.pID = p3.pID) AND (p1.SALARY BETWEEN 20000 AND 80000))JOIN PEOPLE p4ON ((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 Range3from People p`
Post #1439489
 Posted Tuesday, April 09, 2013 4:43 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Friday, November 22, 2013 5:00 PM Points: 528, Visits: 966
 You two are freaky scary sometimes.. :D
Post #1440605
 Posted Tuesday, April 09, 2013 4:45 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:59 AM Points: 22,083, Visits: 28,983
 Erin Ramsay (4/9/2013)You two are freaky scary sometimes.. :DAnd why do you say that??
Post #1440606
 Posted Tuesday, April 09, 2013 6:06 PM
 Old Hand Group: General Forum Members Last Login: Today @ 1:40 PM Points: 350, Visits: 2,583
 It's because they know how to use the Force, and we don't...Silly Us!
Post #1440617

 Permissions