need count of Range in sql query [duplicate]

Question

I have a problem details at http://sqlfiddle.com/#!3/8e018/1

I have a table of members having marks of all the students. I am trying to get the count of all the students in ranges like

0-9 = number of students 9,

10 -19 = number of students 0 and so on up to 100.

Plus if some body can point to an nice tutorial on case statements will be very good

The answers given are fine. but my ranges are fixed. i have to show 0 as well if there is no applicant This is the main difference what my question is having. like i have show also the category.

Answer 1

You don't need a CASE statement. You can group by the result of integer division.

SELECT 10 * ( marks / 10 )     AS start_range,
       10 * ( marks / 10 ) + 9 AS end_range,
       count(*)                AS COUNT
FROM   testTable
GROUP  BY marks / 10 

This will group

0  -  9
10 - 19
/* ...*/
90 - 99
100 - 109

If you don't want 100 to be in a range on its own (as the only possible value in the end range) you need to define the requirements more clearly.

To include all ranges you can use

SELECT CAST(10 * ( G.Grp ) AS VARCHAR(3)) + '-' 
                          + CAST(10 * ( G.Grp ) + 9 AS VARCHAR(3)) AS range,
       count(T.id)                                                 AS Count
FROM   (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) G(Grp)
       LEFT JOIN [dbo].[testTable] T
         ON G.Grp = T.marks / 10
GROUP  BY G.Grp 

SQL Fiddle