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 5, 2013 2:36 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, June 21, 2016 10:32 PM Points: 58, Visits: 127
 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 5, 2013 2:40 PM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 5:19 PM Points: 14,549, Visits: 38,413
 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--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!
Post #1439488
 Posted Friday, April 5, 2013 2:42 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 5:16 PM Points: 23,522, Visits: 37,745
 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 9, 2013 4:43 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Monday, October 24, 2016 9:51 AM Points: 551, Visits: 1,099
 You two are freaky scary sometimes.. :D
Post #1440605
 Posted Tuesday, April 9, 2013 4:45 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 5:16 PM Points: 23,522, Visits: 37,745
 Erin Ramsay (4/9/2013)You two are freaky scary sometimes.. :DAnd why do you say that??
Post #1440606
 Posted Tuesday, April 9, 2013 6:06 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 6:56 PM Points: 2,135, Visits: 12,277
 It's because they know how to use the Force, and we don't...Silly Us!
Post #1440617

 Permissions