mysql - 按 SUM 值范围分组 SQL

Question

我有一张用户 ID 和付款金额表。我想对所有 UserID 的付款求和，然后将它们分组到范围中。范围为“小”(总计介于 1 - 499 英镑之间)、“中”(500 - 4,999 英镑)和“大”(5,000 英镑以上)

因此，如果 UserID 已支付 4 次不同的 100 英镑付款，那么他将显示为 UserID:76867 Range Amount 'Small'

我当前的查询为我提供了每个 UserID 及其总数所属的范围

SELECT 
 SERIALNUMBER ,  Case when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 1 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 500 then 'small' when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 499 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 5000 then 'medium' when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 5000 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 10000000 then 'large' END  AS 'Sum of PAYMENTAMOUNT'  
FROM BATCHDETAIL
WHERE  (DATEOFPAYMENT > '2017/07/31') AND (DATEOFPAYMENT < '2018/08/01')  
GROUP BY SERIALNUMBER

但是我现在需要的是总结每个范围内有多少个 userID

Range  |  Count of UserIDs  
-------|----------------  
Small  |       23  
-------|----------------  
Medium |       08

(对格式错误表示歉意)。任何帮助/问题将一如既往地不胜感激!谢谢李

Answer 1

您可以将 select 语句放入子查询中。我假设 Serialnumber 是一个 UserID，并且您想要对每个组中的不同用户进行计数。

     Select [Sum of PAYMENTAMOUNT] as 'Range', 
count(distinct Serialnumber) as  'Count of UserIDs' FROM
            (
            SELECT  
             SERIALNUMBER ,  
        Case when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 1 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 500 then 'small' 
        when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 499 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 5000 then 'medium' 
        when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 5000 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 10000000 then 'large' END  AS 'Sum of PAYMENTAMOUNT'  
            FROM BATCHDETAIL
            WHERE  (DATEOFPAYMENT > '2017/07/31') AND (DATEOFPAYMENT < '2018/08/01')  
        GROUP BY SERIALNUMBER
        ) s
 group by [Sum of PAYMENTAMOUNT]