mysql - SQL SUM 计数大小写

Question

我正在进行一些交易分析。我想按每笔交易的数量查看交易数量。下面是想要的数据和结果。

table name: trans_cust

store_id  trans_id  product    qty
A            123     milk       1
A            123    chocolate   1
A            123    candy       1
A            456    milk        4
A            789    candy       1
B            321    chocolate   3
B            321    napkin      1
B            654   blueberry    2
C            987    candy       6

Result

Quantity    num_Transaction
1               1
2               1
3               1
4+              3

我尝试了以下方法，但没有成功。

select count(distinct(trans_id)) as num_transation, case
when sum(quantity) = 1 then '1'
when sum(quantity) = 2 then '2'
when sum(quantity) = 3 then '3'
when sum(quantity)>= 5 then '5+' else 'other' end as Quantity

from (select sum(quantity) as Quantity from trans_cust) 

请问大家可以帮忙吗？谢谢。

Answer 1

这是你想要的吗？

SELECT CASE WHEN s.quantity >= 4 THEN '4+' 
            WHEN s.quantity = 1 THEN '1'
            WHEN s.quantity = 2 THEN '2'
            WHEN s.quantity = 3 THEN '3'
       END as quantity
      ,count(*)
FROM (
    SELECT t.trans_id,sum(t.quantity) as quantity 
    FROM trans_cust t
    group by t.trans_id) s
GROUP BY CASE WHEN s.quantity >= 4 THEN '4+' 
            WHEN s.quantity = 1 THEN '1'
            WHEN s.quantity = 2 THEN '2'
            WHEN s.quantity = 3 THEN '3'
       END

将其设为 varchar 列会使代码有点困惑，您也可以这样做:

SELECT p.quantity,count(*) FROM (
    SELECT CASE WHEN s.quantity >= 4 THEN '4+' 
                WHEN s.quantity = 1 THEN '1'
                WHEN s.quantity = 2 THEN '2'
                WHEN s.quantity = 3 THEN '3'
           END as quantity
    FROM (
        SELECT t.trans_id,sum(t.quantity) as quantity 
        FROM trans_cust t
        group by t.trans_id) s) p
GROUP BY p.quantity