SQL query to group items with their quantity then show a total quantity

I have the following data that I’d like to rearrange using an SQL query:

I’d like to group the same order_id to show it like the following:

99996 | 63 – 2, 62 – 2 | 4

So that’s looking for the items in the same order, showing the product id and quantity, then showing the total of all the quantities for each order.

Usually I’d attempt this and show my code but I don’t even know where to start!

Answer

Use two levels of aggregation:

select order_id, group_concat(product_id, ' - ', cnt separator ', ') as products, sum(cnt) as total
from (select order_id, product_id, count(*) as cnt
      from t
      group by order_id, product_id
     ) op
group by order_id;