SQL订单分类统计查询求助：需添加计数列并按计数降序

直接添加计数与分组逻辑，同时用更易读的CASE WHEN替代嵌套IF，满足需求：

SELECT 
    CASE
        WHEN Total_order_amount <= 10000 THEN 'Regular Order'
        WHEN Total_order_amount > 60000 THEN 'Expensive Order'
        ELSE 'Not So Expensive Order'
    END AS categories,
    COUNT(*) AS order_count
FROM Orders
GROUP BY categories
ORDER BY order_count DESC;

关键改动说明 #

• 新增计数列：用COUNT(*)统计每个分类下的订单总数，命名为order_count
• 分组聚合：通过GROUP BY categories将同分类订单归组，才能实现分类计数
• 排序逻辑：ORDER BY order_count DESC实现按订单数量从多到少排序
• 可读性优化：嵌套IF替换为CASE WHEN，逻辑层级更清晰，后续维护更方便

如果偏好保留IF语法，也可以写成：

SELECT 
    IF(Total_order_amount <= 10000, 'Regular Order', 
       IF(Total_order_amount > 60000, 'Expensive Order','Not So Expensive Order')
    ) AS categories,
    COUNT(*) AS order_count
FROM Orders
GROUP BY categories
ORDER BY order_count DESC;

内容的提问来源于stack exchange，提问作者Rohan Mourya