Posted by mutemode on 08/23/06 19:14

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?

• Next in forum: Re: Case help and Identity help
• Prev in forum: Efficient Store Proc for Paging Very large DataSet using Cursor Approach
• Thread view: Case help and Identity help

[Reply to this message]