时间戳

首页 > 解决方案 > 如何使用 select sum() 按组选择值？

mysql - 如何使用 select sum() 按组选择值？

问题描述

我需要按组选择值（从 0 到 10，从 10 到 50，超过 50）。

user_id     amount
  1           20
  1           40
  2           5
  3           30
  3           1

为什么这个查询不能正常工作？

select (select sum(amount)),
case 
when (select sum(amount))<10 then '0-10' 
when (select sum(amount))>=10 and (select sum(amount))<50 then '10-20' 
else '>50' end as total_amount, count(distinct user_id) 
from table
group by
case 
when (select sum(amount))<10 then '0-10' 
when (select sum(amount))>=10 and (select sum(amount))<50 then '10-20' 
else '>50' end as total_amount, count(distinct user_id);



 output
diapason number_of_users
0-10      1
10-50     1
>50       1

请给我提示

标签： mysqlsqldatabasejoinselect

解决方案

您的查询有许多问题，但主要是它不起作用，因为您需要在排序到范围之前按用户进行求和。试试这个：

SELECT CASE  
         WHEN amount BETWEEN  0 AND  9 THEN ' 0-10'
         WHEN amount BETWEEN 10 AND 50 THEN '10-50'
         ELSE '>50' END AS diapason,
       COUNT(*) AS number_of_users
FROM (SELECT SUM(amount) AS amount 
      FROM payments 
      GROUP BY user_id) p
GROUP BY diapason;

输出

diapason    number_of_users
0-10        1
10-50       1
>50         1

推荐阅读