Not able to order column in mysql

Question

I am using this table from the Northwind dataset (can be generated from query below)

+-----------+-----------+
| NumOrders | CustCount |
+-----------+-----------+
|         1 |         1 |
|         2 |         2 |
|         3 |         7 |
|         4 |         6 |
|         5 |        10 |
|         6 |         8 |
|         7 |         7 |
|         8 |         4 |
|         9 |         5 |
|        10 |        11 |
|        11 |         4 |
|        12 |         3 |
|        13 |         3 |
|        14 |         6 |
|        15 |         3 |
|        17 |         1 |
|        18 |         3 |
|        19 |         2 |
|        28 |         1 |
|        30 |         1 |
|        31 |         1 |
+-----------+-----------+`

And I want to write a query to provide a histogram of the number of x people who made y number of orders

select 
    case 
        when NumOrders > 0 and NumOrders <= 5 then '0 - 5'
        when NumOrders > 5 and NumOrders <=10 then '6 - 10'
        else '10+' 
    end as Bucket,
    CustomerCount = sum(CustCount)
from (
    select 
        NumOrders,
        CustCount = count(*) 
    from (
        select * 
        from (
            select 
                CustomerID, 
                count(*) as NumOrders
            from orders 
            group by CustomerID
            ) c
        ) b
    group by NumOrders
    )a
group by 
(
    case 
        when NumOrders > 0 and NumOrders <= 5 then '0 - 5'
        when NumOrders > 5 and NumOrders <=10 then '6 - 10'
        else '10+' 
    end 
)

From the query above I am getting this output, which is ordered incorrectly.

+--------+---------------+
| Bucket | CustomerCount |
+--------+---------------+
| 0 - 5  |            26 |
| 10+    |            28 |
| 6 - 10 |            35 |
+--------+---------------+

I would like it to be ordered as

+--------+---------------+
| Bucket | CustomerCount |
+--------+---------------+
| 0 - 5  |            26 |
| 6 - 10 |            35 |
| 10+    |            28 |
+--------+---------------+

Can someone suggest how to order it correctly?

score 1 · Answer 1 · answered Feb 04 '19 at 15:12

1

You just need

Order by NumOrders

at the very end of your query

answered Feb 04 '19 at 15:12

Derviş Kayımbaşıoğlu

28,492
4
50
72

Hi Simonare, No that would not work. That way I will have to add NumOrders in the group by clause. and resulting output is not what I am looking for. – NMB Feb 04 '19 at 15:20

score 0 · Answer 2 · answered Feb 04 '19 at 16:15

I can't see what part of the problem this fails to solve...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(NumOrders SERIAL PRIMARY KEY
,CustCount INT NOT NULL
);

INSERT INTO my_table VALUES
(1 ,1),
(2 ,2),
(3 ,7),
(4 ,6),
(5 ,0),
(6 ,8),
(7 ,7),
(8 ,4),
(9 ,5),
(10,1),
(11,4),
(12,3),
(13,3),
(14,6),
(15,3),
(17,1),
(18,3),
(19,2),
(28,1),
(30,1),
(31,1);

SELECT CASE WHEN numorders BETWEEN 0 AND  5 THEN '0-5'
            WHEN numorders BETWEEN 6 AND 10 THEN '6-10'
            ELSE '+10' END bucket
      , COUNT(*) total
   FROM my_table
  GROUP
     BY bucket
  ORDER
     BY numorders;
+--------+-------+
| bucket | total |
+--------+-------+
| 0-5    |     5 |
| 6-10   |     5 |
| +10    |    11 |
+--------+-------+

Hi Strawberry, the output of this table 'counts' the number of cases in each bucket. I need the 'sum' of the number of customers in each bucket. (as mentioned in the desired output in my question) — NMB, Feb 04 '19 at 17:30

Not able to order column in mysql

2 Answers2