WooCommerce: How to Get Total Sales and Orders of All Products
Please note that the information in this post may no longer be accurate or up to date. I recommend checking more recent posts or official documentation for the most current information on this topic. This post has not been updated and is being kept for archival purposes.
UPDATE 11/5/2015: A lot has changed in WooCommerce since this article was published so I have updated a few of the functions below so they are up to date with the latest version 🙂 – if you’re looking for how to deal with order data, check out this article.
Perhaps you have to display some stats on the frontend and need the total sales number to use with some math. Here is how I found out how to tap into the data provided by WooCommerce’s admin reporting functionality.
Data Points:
- Total Sales
- Total Orders
- Total of All Shipping Costs
- Total of All Discounts Used
- Total of All Items Ordered
- Total Sales Per Item
WooCommerce comes with built-in reporting in the WordPress admin dashboard. The admin reporting code can be found in woocommerce/admin/woocommerce-admin-reports.php. If you have been selling on WooCommerce for awhile, you should be familiar with the dashboard reporting screen:
How to Get Total Sales Number
The following code will query your WordPress database and retreive the total sales of your site. Be sure to update the function name so it’s unique.
function my1234_get_total_sales() {
global $wpdb;
$order_totals = apply_filters( 'woocommerce_reports_sales_overview_order_totals', $wpdb->get_row( "
SELECT SUM(meta.meta_value) AS total_sales, COUNT(posts.ID) AS total_orders FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
WHERE meta.meta_key = '_order_total'
AND posts.post_type = 'shop_order'
AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )
" ) );
return absint( $order_totals->total_sales);
}
Once this is in a custom functionality plugin, or if it has to be, your theme’s functions.php file then you are ready to use it. Just echo it out within your template files or wherever you need it to show.
Get Total of All Shipping Costs
$shipping_total = apply_filters( 'woocommerce_reports_sales_overview_shipping_total', $wpdb->get_var( "
SELECT SUM(meta.meta_value) AS total_sales FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE meta.meta_key = '_order_shipping'
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
" ) );
Total of All Discounts Used
$discount_total = apply_filters( 'woocommerce_reports_sales_overview_discount_total', $wpdb->get_var( "
SELECT SUM(meta.meta_value) AS total_sales FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE meta.meta_key IN ('_order_discount', '_cart_discount')
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
" ) );
Total of All Items Ordered
$order_items = apply_filters( 'woocommerce_reports_sales_overview_order_items', absint( $wpdb->get_var( "
SELECT SUM( order_item_meta.meta_value )
FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_qty'
" ) ) );
Total Sales Per Item
The following code will return an array of objects containing product ids and their corresponding total sales.
$order_items = apply_filters( 'woocommerce_reports_top_earners_order_items', $wpdb->get_results( "
SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
WHERE posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('" . implode( "','", apply_filters( 'woocommerce_reports_order_statuses', array( 'completed', 'processing', 'on-hold' ) ) ) . "')
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_line_total'
AND order_item_meta_2.meta_key = '_product_id'
GROUP BY order_item_meta_2.meta_value
" ));
.. then using the array returned above you could loop through the results and do basic math to get your totals:
$totalFoodDonations = 0;
$foodProducts = array(48, 61, 103);
//Gather Total for Food Items
foreach ($order_items as $item) {
if (in_array($item->product_id, $foodProducts)) {
$totalFoodDonations = $item->line_total + $totalFoodDonations;
}
}
Know of a better way to do this? Please share! Use the comments below to describe how you grab the total sales, orders and additional reporting data from WooCommerce.
Please keep in mind I have not done any security testing on this code but I believe it’s secure enough.
Discover more from Devin.org
Subscribe to get the latest posts sent to your email.
Thanks!
Thanks so much! This is exactly what I was looking for. Is there any way to output these with out it showing float() or int() text? Thanks so much!
Hi!
Super! I’ve been looking for something like this for some time now. I can’t see if it is included here, but is there an option that lets you also choose total sales for products within a specific date span/order span/or order status? Also, I am no expert in code… Can I put this code in “woocommerce-admin-reports.php” under my child-theme? Can I also maybe generate a printable version of the output?
Would highley appreciate your assistance!
/Berra
You can put this code in your custom template files or create a function within your theme’s functions.php file. Adjusting the reports for dates and making it printable would simply require some adjustments to the SQL query and output methods.
Thanks but this might be too complicated for non tech people. A good woocommerce order statisctics with woocommerce total orders i found http://codecanyon.net/item/woocommerce-today-orders-and-todate-orders/7132372?ref=albdesign . Seems it does the same thing
Thanks
You should probably add in a:
global $wpdb;
Before the rest of the code 🙂
Nice post!
Another approach is to plug the numbers to the external reporting system so you can compare and combine it with other sources (esp. Google analytics) and circumvent the limitations of the Woocommerce reports.
I am using kpiwatchdog.com for that, plan with 5 metrics is free. Here is blog about the integration with woocommerce http://blog.kpiwatchdog.com/2014/04/advanced-analytics-for-wordpress-and.html
I had the store total code and the total sales per item code working, and they both worked perfectly. But then WooCommerce updated to version 2.2.2 and it stopped working. Doesn’t display anything now on the site I’m working on or my test site. I know this post is a year old, but do you happen to have any ideas? Tried downgrading back a couple of versions, still nothing.
EDIT: Updated to 2.2.3 in the hopes that it might fix the issue. still not displaying anything.
Thanks for any help
Hi Devin. I was using this code to display an amount raised for charity during a limited time sale. With the recent update to woocommerce, they have depreciated shop_order_status and possibly some other things as order status has been changed to a post type. Do you know what I would need to do to get the code working again? Thanks so much for any help you can offer and thanks for sharing how to do this in the first place. Have a great day!
In the develop Woocommerce blog (http://develop.woothemes.com/woocommerce/2014/08/wc-2-2-order-statuses-plugin-compatibility/) there is an update to dp query you can use now that shop_order_status has been deprecated.
This worked for me:
$order_items = apply_filters( ‘woocommerce_reports_top_earners_order_items’, $wpdb->get_results( ”
SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
WHERE posts.post_type = ‘shop_order’
AND posts.post_status IN ( ‘” . implode( “‘,'”, array( ‘wc-completed’, ‘wc-processing’, ‘wc-on-hold’ ) ) . “‘ )
AND order_items.order_item_type = ‘line_item’
AND order_item_meta.meta_key = ‘_line_total’
AND order_item_meta_2.meta_key = ‘_product_id’
GROUP BY order_item_meta_2.meta_value
” ));
Hey Bill – thanks for the tip!
Oh man, this is the key to everything! Thank you so, so much for posting this.
I don’t understand please, help me to explain it again .
Here is my updated coding for outputting the total sales with the WC update that broke the code in the post:
function get_total_sales() {
global $wpdb;
$order_totals = apply_filters( ‘woocommerce_reports_sales_overview_order_totals’, $wpdb->get_row( ”
SELECT SUM(meta.meta_value) AS total_sales, COUNT(posts.ID) AS total_orders FROM {$wpdb->posts} AS posts
LEFT JOIN {$wpdb->postmeta} AS meta ON posts.ID = meta.post_id
WHERE meta.meta_key = ‘_order_total’
AND posts.post_type = ‘shop_order’
AND posts.post_status IN ( ‘” . implode( “‘,'”, array( ‘wc-completed’, ‘wc-processing’, ‘wc-on-hold’ ) ) . “‘ )
” ) );
return absint( $order_totals->total_sales);
}
Do you know how to code for outputting total items purchased?
You above codes working so awesome! thanks 🙂
Just confirming that this snippet works – after a couple hours of head-scratching and frustration, I finally came across this and it was a life saver! See it in action: https://supershots.org/supermodel/ – upper right says ”
$[amount] raised so far for our amazing causes!” Here’s the pastebin of my snippet as it’s currently in use: https://pastebin.com/fArMnnTw
How about total sales for a specific category? I’ve been trying to figure that one out for a long while. I can’t quite nail down how to do so. I can only get per product totals to show.
Hi, Is this code working with Woocommrce 2.4.8?
I try but nothing display.
Devin, my man, you’re a life saver. This is fantastic.
I’m using this in a bespoke donation woocommerce build, so needed to get the current total ‘donated’ to each item. For anyone else wanting to do this, here’s the code.
function dw_product_totals() {
global $wpdb;
$post = get_post( $post_id );
$current_product = get_the_ID($post);
$order_items = apply_filters( ‘woocommerce_reports_top_earners_order_items’, $wpdb->get_results( ”
SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id
LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
WHERE posts.post_type = ‘shop_order’
AND posts.post_status IN ( ‘” . implode( “‘,'”, array( ‘wc-completed’, ‘wc-processing’, ‘wc-on-hold’ ) ) . “‘ )
AND order_items.order_item_type = ‘line_item’
AND order_item_meta.meta_key = ‘_line_total’
AND order_item_meta_2.meta_key = ‘_product_id’
GROUP BY order_item_meta_2.meta_value
” ));
$current = array($current_product);
foreach($order_items as $item) {
if(in_array($item->product_id, $current)) {
$totalDonations = $item->line_total;
}
}
$totalDonations = number_format($totalDonations, 2, ‘.’, ”);
echo ‘total donations = ‘. $totalDonations;
}
how can I show total price of all completed orders for a customer ?!
how to show make
SQL script to show the vouchers that was redeemed.
Grouped by merchants.
Total value.
For Yesterday.
I also want another script to show the total sales:
Grouped by Merchants
Total Value
Hi, is it possible to show how many user ordered x item and how many of them are girl, boy or other gender on the product page.
I found a GitGist to display the “total sales per product”. I tested this on a product category page and on a single product post, it works great – https://gist.github.com/stephanieland352/182355e34139227bd2762f0281d1afd4