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