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.

Woocommerce Total SalesUPDATE 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:

woocommerce-monthly-sales

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.

Similar Posts