need count of Range in sql query [duplicate]

问题:

This question already has an answer here:

• In SQL, how can you “group by” in ranges? 14 answers

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.

回答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

回答2:

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.

回答3:

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