MySQL – summing up products from several orders

I want to sum up the number of items purchased from several orders. I have this query, however it shows me each order on a separate row. I want have it all added up in one row. This is woocommerce database.

SELECT
  ( SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</br>' )
    FROM wp_woocommerce_order_items i
    JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
    WHERE i.order_id = p.ID AND i.order_item_type = 'line_item') AS 'Items'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order'

Example output:

2 x item1</br>2 x item2</br>1 x item3
4 x item1</br>5 x item2</br>3 x item3

and this is how it should be:

6 x item1</br>7 x item2</br>4 x item3

Answer

You want the aggregation string over all those items, so the aggregation must be the main query. Look up the desired order IDs with IN or EXISTS.

SELECT GROUP_CONCAT(CONCAT(m.meta_value, ' x ', i.order_item_name) separator '</br>' )
FROM wp_woocommerce_order_items i
JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
WHERE i.order_item_type = 'line_item'
AND i.order_id IN
(
  SELECT p.ID
  FROM wp_posts AS p
  JOIN wp_postmeta AS pm ON p.ID = pm.post_id
  WHERE post_type = 'shop_order'
);

Maybe you can remove the join on wp_postmeta in the subquery. That depends on which table the column post_type resides in.

(I hope I got everything right. I just took your query and re-wrote it. I don’t know the data model and I don’t know if a post ID can really equal an order ID, but this is how you use them in your query.)