Find the number of orders related to each order size in MySQL

121
September 08, 2018, at 10:50 PM

Need some help with a MySQL query to be used in a larger database. Simplified here, I need to find the number of orders related to each order size.

I've been trying to get the query to work with a lot of combinations like: COUNT(DISTINCT item) or GROUP_CONCAT(DISTINCT order_id), GROUP BYs, ORDER BYs, HAVING COUNT(DISTINCT item_id), etc. but it's not turning out like I really need it to. Any help toward getting me going in the right direction would be greatly appreciated.

In this example table named items, the person with an order_id of 1 ordered three items, the person with an order_id of 4 ordered only one item, the person with an order_id of 5 ordered two items, etc. At the moment, they can only order up to three items max, but in the future, more items could be added so the query needs to be written in a way that can scale to 4 items, 5 items, etc.

Table name is: items

item_id    order_id    item
-------------------------------
1          1           apple
2          1           orange
3          1           grape
4          2           grape
5          3           apple
6          3           orange
7          4           apple
8          5           orange
9          5           apple
10         6           apple
11         6           orange
12         6           grape
13         7           orange
14         8           grape

In this example, the query result would be:

Number_of_Orders        Total_Order_Size
----------------------------------------
4                       1
2                       2
2                       3
Answer 1

You have to group by twice.

select item_count,count(*)
from (select order_id,count(*) as item_count
      from tbl 
      group by order_id
     ) t
group by item_count
Answer 2

You can use two levels of aggregation:

select num_items, count(*) as num_orders
from (select order_id, count(*) as num_items
      from t
      group by order_id
     ) o
group by num_items
order by num_items;
READ ALSO
Nested SELECT, or am I over-complicating?

Nested SELECT, or am I over-complicating?

I have the following tables:

76
Batch process a Queryset to avoid database connection timeout

Batch process a Queryset to avoid database connection timeout

I've got a process which imports data from a CSV file then processes the data to run various calculations & set values

110
How to find the nearest geohash in mysql?

How to find the nearest geohash in mysql?

Recently I'm using GeoHash to hash the paired geo-coordinates into a hash value and store it in MySQLNow I want to find the nearest hash given the other hash

135
Can't expose mysql tcp service running inside kubernetes cluster publicly using nginx-ingress

Can't expose mysql tcp service running inside kubernetes cluster publicly using nginx-ingress

I ran into a problem exposing a mysql database running inside a kubernetes cluster publiclyThe cluster runs with kops on AWS

120