需要在 sql 查询中的范围计数 [英] need count of Range in sql query

问题描述

我有一个问题的详细信息http://sqlfiddle.com/#!3/8e018/1

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 = 学生人数 9,

10 -19 = 学生人数 0 以此类推，直至 100.

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

给出的答案很好.但我的范围是固定的.如果没有申请人，我也必须显示0这是我的问题的主要区别.就像我也显示了类别.

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.

推荐答案

您不需要 CASE 语句.可以按整数除法的结果分组.

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 

这将分组

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

如果您不希望 100 单独在一个范围内(作为结束范围中唯一可能的值)，您需要更清楚地定义要求.

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 小提琴

这篇关于需要在 sql 查询中的范围计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！