database - Count total number of order item of each order in mysql

Question

I have one table like below

|order_item_id|order_id|customer_id|
|     2       |   30   |    9      | 
|     3       |   30   |    9      | 
|     4       |   30   |    9      | 
|     5       |   30   |    9      | 
|     11      |   32   |    9      | 
|     12      |   32   |    9      | 
|     13      |   32   |    9      | 

here i would like to count total number of order_item_id for each order_id using mysql. please help

Answer 1

Try this:

select order_id, count(*) from t
group by order_id

Edit:

yes this one i knew it, but actually i would like to list out all related records as well, not just count. – user804457

After the requirements changed, then this seems to be what you're looking for:

select * from t t1
join (
  select order_id, count(*) aCount from t
  group by order_id
) t2
on t1.order_id = t2.order_id

Result:

+---------------+----------+-------------+--------+
| ORDER_ITEM_ID | ORDER_ID | CUSTOMER_ID | ACOUNT |
+---------------+----------+-------------+--------+
|             2 |       30 |           9 |      4 |
|             3 |       30 |           9 |      4 |
|             4 |       30 |           9 |      4 |
|             5 |       30 |           9 |      4 |
|            11 |       32 |           9 |      3 |
|            12 |       32 |           9 |      3 |
|            13 |       32 |           9 |      3 |
+---------------+----------+-------------+--------+