mysql - Count Ocurrence of Field whilst being Group By 2

Question

I'm struggling to try to have the count of order id on an item_id row, any help is greatly appreciated!

Data
item_id | order_id
1 | Order_1
2 | Order_1
3 | Order_2
4 | Order_3
Desired Result

item_id | order_id | items_in_order
1 | Order_1 | 2
2 | Order_1 | 2
3 | Order_2 | 1
4 | Order_3 | 1


   SELECT S.item_id, S.`order_id`, S.order_total, C.cnt as items_in_order,

       `order_discount` / C.cnt as item_discount,
                `order_total` / C.cnt  as item_price
FROM `orders` S 
LEFT JOIN (SELECT `item_id`, `order_id`, count(`order_id`) as cnt  FROM `supplier_orders` GROUP BY `order_id`) 
C ON S.`order_id` = C.`order_id` AND S.id = C.item_id

This would produce this with null values
item_id | order_id | items_in_order | item_discount | item_price
3009117 | 3029511 | 2    | 0    | 25 
3009118 | 3029511 | null | null | null 

UPDATE, this now seems to work as intended

SELECT S.`item_id`, S.`order_id`, S.order_total, C.cnt as items_in_order,

       `order_discount` / C.cnt as item_discount,
                `order_total` / C.cnt  as item_price
FROM `orders` S 
INNER JOIN (SELECT `item_id`, `order_id`, count(`order_id`) as cnt  FROM `orders` GROUP BY `order_id`) 
C ON S.`order_id` = C.`order_id` 
GROUP BY S.`item_id`

Answer 1

Your query does not relate to your sample data; however you seem to want aggregation and ranking. In MySQL 8.0, you would do:

select
    row_number() over(order by count(*) desc) rn,
    order_id,
    count(*) items_in_order
from data
group by order_id
order by rn

I named the first column rn (for rank): I find id confusing here, since you already have a column with that name in the table.

In earlier versions, one option uses a session variable instead of row_number():

select @rn := @rn + 1 rn, order_id, items_in_order
from (
    select order_id, count(*) items_in_order
    from data
    group by order_id
    order by items_in_order desc
) t
cross join (select @rn := 0) r
order by items_in_order desc