mysql - SQL SUM COUNT情况

我正在做一些交易分析。
我想按每笔交易数量查看交易数量。
以下是所需的数据和结果。

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)

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

最佳答案

这是你想要的吗？

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

关于mysql - SQL SUM COUNT情况，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46807687/