## Want to categorize the salaries of employee.

 Author Message kiran.vaichalkar Mr or Mrs. 500 Group: General Forum Members Points: 598 Visits: 132 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))' Lowell SSC Guru Group: General Forum Members Points: 185065 Visits: 41569 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! Lynn Pettis SSC Guru Group: General Forum Members Points: 226192 Visits: 40426 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` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Erin Ramsay SSCertifiable Group: General Forum Members Points: 5624 Visits: 1126 You two are freaky scary sometimes.. Lynn Pettis SSC Guru Group: General Forum Members Points: 226192 Visits: 40426 Erin Ramsay (4/9/2013)You two are freaky scary sometimes.. And why do you say that?? ;-) Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) pietlinden SSC-Dedicated Group: General Forum Members Points: 32151 Visits: 15180 It's because they know how to use the Force, and we don't...Silly Us!