WP Gists

Woocommerce number of all orders between dates

SET @prefix = 'wp_';
SET @date_from = '2020-01-01';
SET @date_to = '2020-12-31';

SET @query = CONCAT('SELECT sum(woim.meta_value) number_of_item_sold, woi.order_item_name
    FROM ', @prefix, 'woocommerce_order_itemmeta as woim
    LEFT JOIN ', @prefix, 'woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id
	LEFT JOIN ', @prefix, 'posts as p ON woi.order_id = p.ID
	WHERE woim.meta_key = \'_qty\'
		AND p.post_status = \'wc-completed\'
		AND p.post_date BETWEEN \'', @date_from, '\' AND \'', @date_to, '\'
	GROUP BY woi.order_item_name
	ORDER BY number_of_item_sold DESC');

PREPARE stmt FROM @query;
EXECUTE stmt;