按SUM值范围分组SQL-Java 学习之路

我有一张UserID和付款金额表 . 我想要汇总所有UserID的付款，然后将它们分组到范围内 . 范围是“小”（总计在1英镑到499英镑之间），“中等”（500英镑 - 4,999英镑）和“大”（5,000英镑）

因此，如果UserID已经进行了4次不同的100英镑付款，那么他将显示为UserID：76867 Range Amount'Flow'

我当前的查询为我提供了每个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

（为格式错误道歉） . 任何帮助/问题将永远受到高度赞赏！谢谢李

4 回答

您可以将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]

回复于 2024-04-28T00:51:12+08:00

将您的查询用作子查询，并在“PAYMENTAMOUNT的总和”上再次对其进行分组

select [Sum of PAYMENTAMOUNT] , count(SERIALNUMBER) 
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
  ) as t1
group by [Sum of PAYMENTAMOUNT]

回复于 2024-04-28T00:51:12+08:00

您可以使用原始选择定义CTE（公用表表达式），因此您现在可以轻松地将其结果分组以计算每个范围的外观 .

with Ranges as
(
  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
)

select 'Sum of PAYMENTAMOUNT', count(*) as 'Count of UserIDs'
from Ranges
group by 'Sum of PAYMENTAMOUNT'

回复于 2024-04-28T00:51:12+08:00

最有效的方法是在两个级别（使用子查询）执行此操作：

select
    count(*) as cnt
,   Case 
        when a.total_payment >= 10000000 then 'large+'
        when a.total_payment >= 5000 then 'large' 
        when a.total_payment >= 500 then 'medium' 
        when a.total_payment >= 1 then 'small' 
        else 'too small'
        end AS "Sum of PAYMENTAMOUNT"

from (
    SELECT 
        SERIALNUMBER
    ,   SUM(BATCHDETAIL.PAYMENTAMOUNT) as total_payment
    FROM BATCHDETAIL
    WHERE  (DATEOFPAYMENT > '2017/07/31') AND (DATEOFPAYMENT < '2018/08/01')  
    GROUP BY SERIALNUMBER
) as a
group by "Sum of PAYMENTAMOUNT"

顺便说一句，你错过了一些范围 .

回复于 2024-04-28T00:51:12+08:00

按SUM值范围分组SQL

4 回答

相关问题