对某些分组行的求和

Question

我有一个数据集，它显示了每一类消费者的客人数量和收入(已访问过一次、两次、3次、4次、5次等的消费者)。

看起来是这样的：

​

我对上面那张表的查询是：

SELECT number_of_trans, COUNT(*) AS guest_count, SUM(amount) AS revenue FROM customer_counting GROUP BY number_of_trans ORDER BY number_of_trans

现在，我想把guest_count和收入相加，我想把它们相加到以下几类: 1,2,3,4,5-7和8+。

因此，真正的1,2,3和4将保持不变，但我将在5-7行和从第8行开始的行中求和。

我该怎么做？我想要的表格如下(不要担心值的准确性)：

​

​

我希望我的问题能被理解。

Answer 1

戈登建议的更简单的形式：

SELECT 
  CASE WHEN number_of_trans < 5 THEN cast(number_of_trans as varchar)
    WHEN number_of_trans < 8 THEN '5-7'
    ELSE '8+' 
  END as freq_band, 
  COUNT(*) AS guest_count, 
  SUM(amount) AS revenue 
FROM customer_counting 
GROUP BY CASE WHEN number_of_trans < 5 THEN cast(number_of_trans as varchar)
    WHEN number_of_trans < 8 THEN '5-7'
    ELSE '8+' 
  END
ORDER BY freq_band

Answer 2

使用case和子查询(或CTE)：

SELECT (CASE WHEN number_of_trans < 5 THEN CAST(number_of_trans as varchar)
             WHEN number_of_trans < 8 THEN '5-7'
             ELSE '8+'
        END) as grp,
       SUM(guest_count) as guest_count, SUM(revenue) as revenue
FROM (SELECT number_of_trans, COUNT(*) AS guest_count, SUM(amount) AS revenue
      FROM customer_counting
      GROUP BY number_of_trans
     ) x
GROUP BY grp
ORDER BY MIN(number_of_trans);