WooCommerce: How to Get Total Sales and Orders of All Products
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$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.
24 Comments
reachue
November 11, 2013Thanks!
Griffin Stewart
December 27, 2013Thanks 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!
Berra
February 19, 2014Hi!
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
Devin Walker
February 19, 2014You 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.
Xhveja
March 13, 2014Thanks 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
bryceadams
August 1, 2014You should probably add in a:
global $wpdb;
Before the rest of the code 🙂
Nice post!
raskal25
September 10, 2014Another 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
mayecreate
September 17, 2014I 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
Griffin Stewart
September 21, 2014Hi 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!
Bill Robbins
November 13, 2014In 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
” ));
Devin Walker
November 14, 2014Hey Bill – thanks for the tip!
Ben Hudson
November 24, 2015Oh man, this is the key to everything! Thank you so, so much for posting this.
Admin
February 13, 2015I don’t understand please, help me to explain it again .
jetlej
March 30, 2015Here 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);
}
phraisohn
November 6, 2015Do you know how to code for outputting total items purchased?
You above codes working so awesome! thanks 🙂
Easton Ellsworth
July 26, 2017Just 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
Kahil Nettleton
August 12, 2015How 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.
phraisohn
November 5, 2015Hi, Is this code working with Woocommrce 2.4.8?
phraisohn
November 5, 2015I try but nothing display.
Ben Hudson
November 24, 2015Devin, 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;
}
Mostafa Dehghani
February 17, 2017how can I show total price of all completed orders for a customer ?!
Frinanta Ananda
June 4, 2017how 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
Buğra Can Gençgiyen
November 21, 2017Hi, 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.
FoxyPaco
March 31, 2018I 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