need count of Range in sql query

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.

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... — user1884709, Feb 06 '13 at 13:36
I found an excellent example in an O'Reilly book after asking [this question](http://stackoverflow.com/questions/9786442/why-is-count-returning-multiple-results-in-this-subquery-postgresql). — cms_mgr, Feb 06 '13 at 13:37
This should get you pretty close: http://sqlfiddle.com/#!3/8e018/30 — Aaron Bertrand, Feb 06 '13 at 13:41
I think the accepted answer in the proposed duplicated is more complicated than it needs to be, — Aaron Bertrand, Feb 06 '13 at 13:42

Martin Smith · Accepted Answer · 2013-02-06T13:59:02.663

14

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

edited Feb 06 '13 at 13:59

answered Feb 06 '13 at 13:39

Martin Smith

438,706
87
741
845

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 – user1884709 Feb 06 '13 at 13:42
`CAST(10 * ( marks / 10 ) AS varchar(3)) + '-' + CAST(10 * ( marks / 10 ) + 9 AS varchar(3))` [SQL Fiddle](http://sqlfiddle.com/#!3/8e018/44) – Martin Smith Feb 06 '13 at 13:43
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 – user1884709 Feb 06 '13 at 13:53
Brilliant ... is what i can say. Thanks Martin. – user1884709 Feb 06 '13 at 14:07

cms_mgr · Answer 2 · 2013-02-06T14:04:02.973

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.

score 1 · Answer 3 · answered Feb 06 '13 at 13:58

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

need count of Range in sql query

3 Answers3

Linked