An SQL query

[Juan Pablo González]

Ok... using SQL server, I need to make a 'simple' grouping query. I just need to count the number of customers, and graphing it by credit score, in ranges (less than 400, 400 - 500, 500 - 600, etc.). So I have this:

SELECT CASE
 WHEN RF_2 <= 400 THEN '<= 400'
 WHEN RF_2 <= 500 THEN '401 - 500'
 WHEN RF_2 <= 600 THEN '501 - 600'
 WHEN RF_2 <= 700 THEN '601 - 700'
 WHEN RF_2 <= 800 THEN '701 - 800'
 ELSE '> 800'

END 'Credit Score', COUNT(*) FROM TheTable

GROUP BY RF_2

which I thought was going to work, but nope... I get each record, well, except when the credit score is *exactly* the same in two or more different customers. So I thought, ok, change the 'Group' clause to

GROUP BY 'Credit Score'

but that says that 'GROUP BY expressions must refer to column names that appear in the select list.'

Any ideas ? I just want the query to return

<= 400       	20
401 - 500   	30
501 - 600   	35
601 - 700   	25
701 - 800   	15
> 800       	10

 

[Juan Pablo González]

Ok, if someone needs to know how I did it...

SELECT     Region, COUNT(*) Total
FROM 
 (SELECT 
  CASE 
  WHEN RF_2 <= 400 THEN '<= 400' 
  WHEN RF_2 <= 500 THEN '401 - 500' 
  WHEN RF_2 <= 600 THEN '501 - 600' 
  WHEN RF_2 <= 700 THEN '601 - 700' 
  WHEN RF_2 <= 800 THEN '701 - 800' 
  ELSE '> 800' END AS Region
  FROM TheTable) 
AS Derive(Region)
GROUP BY Region

 

[XL-Dennis]

Nice Juan Pablo