5

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.

Improve this question
5
  • 2
    What range does 100 fall in? Commented Feb 6, 2013 at 13:35
  • i mean 100 can be the final marks value in that column. what i want is in 40 to 50 we have 3 students and so on... Commented Feb 6, 2013 at 13:36
  • 1
    I found an excellent example in an O'Reilly book after asking this question. Commented Feb 6, 2013 at 13:37
  • 1
    This should get you pretty close: sqlfiddle.com/#!3/8e018/30 Commented Feb 6, 2013 at 13:41
  • I think the accepted answer in the proposed duplicated is more complicated than it needs to be, Commented Feb 6, 2013 at 13:42

3 Answers 3

Reset to default
15

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The answer is very good. But just need one modificaiton. I also need to show the range like 0-9 we have 0. This is very necessary in this condition for me
CAST(10 * ( marks / 10 ) AS varchar(3)) + '-' + CAST(10 * ( marks / 10 ) + 9 AS varchar(3)) SQL Fiddle
Again Martin great help. but i also need to show the category which has 0 applicants. i have am doing year wise comparison and for that i need this
Brilliant ... is what i can say. Thanks Martin.
3

Try something like:

SELECT CASE
        WHEN marks IS NULL THEN 'Unknown'
        WHEN marks <= 9 THEN '0-9'
        WHEN marks <= 19 THEN '10-19'
        WHEN marks <= 29 THEN '20-29'
        WHEN marks <= 39 THEN '30-39'
        WHEN marks <= 49 THEN '40-49'
        WHEN marks <= 59 THEN '50-59'
        WHEN marks <= 69 THEN '60-69'
        WHEN marks <= 79 THEN '70-79'
        WHEN marks <= 89 THEN '80-89'
        WHEN marks <= 100 THEN '90-100'
        ELSE 'Over 100'
    END "Bucket",
    COUNT(*) "Number of results"
FROM
    testTable
GROUP BY CASE
        WHEN marks IS NULL THEN 'Unknown'
        WHEN marks <= 9 THEN '0-9'
        WHEN marks <= 19 THEN '10-19'
        WHEN marks <= 29 THEN '20-29'
        WHEN marks <= 39 THEN '30-39'
        WHEN marks <= 49 THEN '40-49'
        WHEN marks <= 59 THEN '50-59'
        WHEN marks <= 69 THEN '60-69'
        WHEN marks <= 79 THEN '70-79'
        WHEN marks <= 89 THEN '80-89'
        WHEN marks <= 100 THEN '90-100'
        ELSE 'Over 100'
    END
ORDER BY
    MIN(marks);

To explain the CASE statement here (as best I can, better people may edit), I always like to put in a NULL option as it can sometimes catch errors in your query. The remaining WHEN statements should be self-explanatory and you can use them to suit your needs. The name "Bucket" is just what your column will be called in the final output so again you can change that as you like. The second column must be an aggregate query, such as COUNT in order for a CASE statement to make sense.

You must repeat the CASE statement, except your name for it, in the GROUP BY statement.

Improve this answer

Comments

1

If you need also empty ranges(i assume) try this:

;WITH Ranges
AS
(
  SELECT 0 n
  UNION ALL
  SELECT n + 1 FROM Ranges
  WHERE n < 9
)
SELECT CAST((n*10) as VARCHAR) + ' - ' + CAST((n*10 + 9) as VARCHAR) [Range], COUNT(marks) Cnt FROM Ranges
  LEFT JOIN [testTable] T
    ON marks >= (n*10) AND marks <= (n*10 + 9)
GROUP BY n*10, n*10 + 9

SQL FIDDLE DEMO

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.