将数字聚合到类中

Question

假设下表：

id    value
-----------
1      3
2      5
3      15
4      18
5      19
6      30

我希望对以下几个组进行分类和统计：

group    amount
---------------
'1-5'      2
'6-10'     0
'11-20'    3
'21-50'    1

我如何在一个查询中做到这一点？

Answer 1

在以下情况下，最简单的方法是将组写入案例中：

SELECT grp, count(*)
FROM
(
 SELECT
  CASE
    WHEN value BETWEEN 1 AND 5 THEN '1-5'
    WHEN value BETWEEN 6 AND 10 THEN '6-10'
    ...and so on...
    ELSE '21-50' 
  END as grp
 FROM
  sourcetable
)a
GROUP BY grp

从本质上讲，您是根据值所在的范围将值从一种形式转换为另一种形式，然后对转换后的值进行计数。注意，我在21-50的范围内使用了一个ELSE，假设值永远不会超过50。如果是这样的话，你需要改变范围，否则，从技术上讲，100也将被归入"21-50“

Answer 2

另一种方法是使用生成的范围表：

;WITH CTE_Groups AS (
    SELECT 1 AS min, 5 AS max, '1-5' AS grp
    UNION ALL
    SELECT 6 AS min, 10 AS max, '6-10' AS grp
    UNION ALL
    SELECT 11 AS min, 20 AS max, '11-20' AS grp
    UNION ALL
    SELECT 21 AS min, 50 AS max, '21-50' AS grp
)
SELECT
    G.grp,
    COUNT(T.id) AS amount
FROM
    CTE_Groups G
LEFT OUTER JOIN My_Table T ON T.value BETWEEN G.min AND G.max
GROUP BY
    G.grp, G.min
ORDER BY
    G.min