SQL 查询以查找特定价格范围内的总数

Question

需要您在SQL查询方面的帮助，我应该如何编写它？我有一个包含total_value和order_id的表格，想找到总价值在 0-50、51-100、超过 100 范围内的订单数量

谢谢！

Answer 1

你想要一个案例陈述。

像这样：

select count(order_id),
       value_range
from 
     (select order_id,
            total_value,
            case when total_value between 0 and 50 then '0-50'
                when total_value between 51 and 100 then '51-100'
                when total_value > 100 then '100'
            end as value_range
       from table)a
group by value_range

Answer 2

也许这对你有用：

select count(order_id),
       valueRange
from 
     (select order_id,
            total_value,
            case when total_value between 0 and 50 then '0-50'
                when total_value between 51 and 100 then '51-100'
                when total_value > 100 then '100'
            end as valueRange
       from table)a
 Group by valueRange

Answer 3

使用条件 SUM：

SELECT SUM(CASE WHEN Total_Value BETWEEN 0 AND 50 THEN 1 ELSE 0 END) As Range1
    ,  SUM(CASE WHEN Total_Value BETWEEN 51 AND 100 THEN 1 ELSE 0 END) As Range2
    ,  SUM(CASE WHEN Total_Value > 100 THEN 1 ELSE 0 END) As Range3
FROM your_table