Woocommerce display total retail sales per product

Question

I already asked this question here and got a partially working answer. But to recap from my previous question. I am querying for woocommerce products in a post query and then displaying them in a list for a leaderboard. I also need to display the individual retail amount sold for each product. When I say retail amount, I don't mean the quantity sold, I mean the total dollar amount sold. I now have working code to get the total retail sold for the whole store, which is pretty awesome.

 // All-time Sales for entire store
$query            = array();
$query['fields']  = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
$query['join']    = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
$query['where']   = "WHERE posts.post_type IN ( '" . implode( "','", wc_get_order_types( 'reports' ) ) . "' ) ";
$query['where']  .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
$query['where']  .= "AND postmeta.meta_key   = '_order_total' ";

$sales = $wpdb->get_var( implode( ' ', $query ) );

Now the issue is, I cant seem to get this to work for an individual product. At the moment I have this code:

$product_id = 10;
$query            = array();
$query['fields']  = "SELECT SUM( postmeta.meta_value ) FROM {$wpdb->posts} as posts";
$query['join']    = "INNER JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id ";
$query['where']   = sprintf( "WHERE posts.ID = %n", $product_id );
$query['where']  .= "AND posts.post_status IN ( 'wc-" . implode( "','wc-", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "' ) ";
$query['where']  .= "AND postmeta.meta_key   = '_order_total' ";

$sales = $wpdb->get_var( implode( ' ', $query ) );

But, unfortunately, it displays only 0's. Is there anyone out there that can help A. fix the code for the individual product, or B. find a better way to do this on a product to product basis?

Thank you for any help on the subject.

Ive been going crazy about this. Does anyone have any ideas out there? — Tyler Ernst, Oct 01 '14 at 19:07
mod_fcgid: stderr: WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND posts.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND pos' at line 1 for query SELECT SUM( postmeta.meta_value ) FROM wp_posts as posts INNER JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id WHERE posts.ID = AND posts.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND postmeta.meta_key = '_order_total' — Tyler Ernst, Oct 01 '14 at 19:42
made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php') — Tyler Ernst, Oct 01 '14 at 19:43
Still at a stand still on this. Does anyone out there have anything that could help with this situation? — Tyler Ernst, Oct 07 '14 at 14:37

score 0 · Answer 1 · answered Feb 05 '17 at 00:20

I was wondering how to do this also, and this is what I came up with.

SELECT SUM( order_item_meta__line_total.meta_value) as order_item_amount FROM wp_posts AS posts INNER JOIN wp_woocommerce_order_items AS order_items ON posts.ID = order_items.order_id INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__line_total ON (order_items.order_item_id = order_item_meta__line_total.order_item_id)  AND (order_item_meta__line_total.meta_key = '_line_total') INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__product_id_array ON order_items.order_item_id = order_item_meta__product_id_array.order_item_id WHERE posts.post_type   IN ( 'shop_order','shop_order_refund' ) AND posts.post_status   IN ( 'wc-completed','wc-processing','wc-on-hold') AND ( ( order_item_meta__product_id_array.meta_key   IN ('_product_id','_variation_id') AND order_item_meta__product_id_array.meta_value IN ('".$product_id."') ))

You might need to construct this query as array as above .

Woocommerce display total retail sales per product

1 Answers1