Case help and Identity help

Giganews Newsgroups
Subject: Case help and Identity help
Posted by:  mutemo‚Ķ@gmail.com
Date: 23 Aug 2006

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?

Replies