|
|
Posted by Roy Harvey on 08/23/06 19:34
Consider creating a table Brackets, which would allow:
SELECT B.bracket, count(income) AS number
FROM Brackets as B
LEFT OUTER JOIN #persons as P
ON O.income BETWEEN B.FromIncome AND B.ToIncome
GROUP BY B.bracket
ORDER BY min(income) ASC
You will have to add one for negative numbes, and another for positive
numbers > 1000, both assigned 'Other' for the bracket column.
Roy Harvey
Beacon Falls, CT
On 23 Aug 2006 12:14:02 -0700, mutemode@gmail.com wrote:
>I have this query
>
>SELECT 'bracket' = CASE
>WHEN income BETWEEN 0 AND 49 THEN '0-49'
>WHEN income BETWEEN 50 AND 99 THEN '50-99'
>WHEN income BETWEEN 100 AND 499 THEN '100-499'
>WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
>ELSE 'Other' END, count(income) AS number
>FROM #persons
>GROUP BY CASE
>WHEN income BETWEEN 0 AND 49 THEN '0-49'
>WHEN income BETWEEN 50 AND 99 THEN '50-99'
>WHEN income BETWEEN 100 AND 499 THEN '100-499'
>WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
>ELSE 'Other' END
>ORDER BY min(income) ASC
>
>which returns
>
>bracket number
>-------- -----------
>50-99 4
>100-499 4
>500-1000 2
>
>I want it to return this
>
>bracket number
>-------- -----------
>0-49 0
>50-99 4
>100-499 4
>500-1000 2
>Other 0
>
>Showing that there are no incomes within the 0-49 category and 0
>incomes in the other category. Halp?
>
>
>AND
>
>I need to get the numbers 1-1000 into a table called #thousand using
>the identity function. Help?
Navigation:
[Reply to this message]
|