MYSQL group by when greater than X

Question

Let me explain my problem.
In mysql, I have an order table like that (simplified) :

+------------+-------+
| customerID | price | 
+------------+-------+
| 10         | 10    |
| 20         | 10    |
| 20         | 10    |
| 30         | 10    |
| 30         | 10    |
| 30         | 10    |
| 120        | 10    |
| 120        | 10    |
| 130        | 10    |
| 130        | 10    |
| 130        | 10    |
| 130        | 10    |
+------------+-------+

I want number of customer by number of order with total price.

+-----------------------------+--------------------+-------------+  
| number_of_order_by_customer | number_of_customer | total_price |  
+-----------------------------+--------------------+-------------+  
| 1                           | 1                  | 10          |  
| 2                           | 2                  | 40          |  
| 3 and more                  | 2                  | 70          |  
+-----------------------------+--------------------+-------------+

we can read this table like :
- 1 customer ordered 1 time for 10 euros (customer 10)
- 2 customers ordered 2 times for a total of 40 euros (customer 20 and customer 120)
- 2 customers ordered 3 or more times for a total of 70 euros (customer 30 (3 times) and customer 130 (4 times) )

My problem is the "3 and more" With this request I can have 1,2,3,4 times but I dont't know how to group 3 and 4 times

SELECT 
    number_of_order_by_customer, COUNT(number_of_order_by_customer) AS number_of_customer, SUM(price) AS total_price 
FROM
(
    SELECT
        COUNT(o.customerID ) AS number_of_order_by_customer, sum(o.price ) AS price
    FROM 
        order o
    GROUP BY o.customerID 
) AS tmp

GROUP BY number_of_order_by_customer
;

I get :

+-----------------------------+--------------------+-------------+  
| number_of_order_by_customer | number_of_customer | total_price |  
+-----------------------------+--------------------+-------------+  
| 1                           | 1                  | 10          |  
| 2                           | 2                  | 40          |  
| 3                           | 1                  | 30          |  
| 4                           | 1                  | 40          |
+-----------------------------+--------------------+-------------+

Does anyone know how to get "3 and more" in the same request ?

score 0 · Accepted Answer · answered Dec 12 '19 at 11:50

Use case when expression-

SELECT 
    case when number_of_order_by_customer>=3 then '3 or More' else cast(number_of_order_by_customer as varchar(10)) end as number_of_order_by_customer, COUNT(nb) AS number_of_customer, SUM(price) AS total_price 
FROM
(
    SELECT
        COUNT(o.customerID ) AS number_of_order_by_customer, sum(o.price ) AS price
    FROM 
        order o
    GROUP BY o.customerID 
) AS tmp

GROUP BY case when number_of_order_by_customer>=3 then '3 or More' else cast(number_of_order_by_customer as varchar(10)) end

Super, but cast as varchar don't work. it as to be convert as char https://stackoverflow.com/a/15368838/12524340 — Devinc, Dec 12 '19 at 13:28

MYSQL group by when greater than X

1 Answers1