SQL-Group by ID´s from tables-CodePudding

So i have 2 tables let's say

Orders(order_id,etc...) and
order_items(order_id,...)

I want to get an overview of how many orders have x amount of items by grouping the id from orders with the x amount of ids from order items

So lets say atm I have this scenario:

orders:

order_id	???	???
11	1	Pending
33	3	Pending
44	3	Pending
22	2	Pending
444	4	Pending

order_items:

order_id	???	???	???	???
11	7	32	132	469,99
22	5	192	124	519,99
22	7	27	92	800,74
33	11	6	128	849,99
33	10	95	106	109,99
33	5	271	148	549,59
44	9	284	138	54,99
44	8	174	117	798,26
44	6	131	34	279,99
44	11	271	58	549,59
444	9	284	138	54,99
444	8	174	117	798,26
444	6	131	34	279,99
444	11	271	58	549,59

With this query:

SELECT  count(o.order_id) as ORDER_count,
       (SELECT count(*)
        FROM order_items  ol
        WHERE o.order_id = ol.order_id) AS ORDER_ITEMS_count
FROM orders o, order_items  ol
 WHERE o.order_id = ol.order_id
GROUP BY o.order_id,ol.order_id;

I'm getting:

ORDER_COUNT	ORDER_ITEMS_COUNT
4	4
1	1
2	2
3	3
4	4

and i'm expecting something like:

ORDER_COUNT	ORDER_ITEMS_COUNT
5	5
1	1
2	2
3	3

Can you please point out what am I missing?

Thankx for the help

CodePudding user response：

I want to get an overview of how many orders have x amount of items by grouping the id from orders with the x amount of ids from order items

First select all orders and count their items. Then use this result to count how many orders share a number of items.

SELECT number_of_items, COUNT(*) AS how_many_orders
FROM
(
  SELECT o.order_id, COUNT(oi.order_id) as number_of_items
  FROM orders o
  LEFT OUTER JOIN order_items ON oi.order_id = o.order_id
  GROUP BY o.order_id
) orders_with_item_counts
GROUP BY number_of_items
ORDER BY number_of_items;

If every order has at least one item (which seems likely), you can change the LEFT OUTER JOIN to an INNER JOIN.