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
Advertisement
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.)