Get WooCommerce Product total sales amount

I have created a shortcode that will returns all products in a loop. I am looking for a way to get the total sales for each product. Right now i’m still having hard time finding the solution.

This is my code:

    function loop_ffh_function() 
    {
        ?>

        <div class="row">
    <?php
    $args = array(
    'numberposts' => -1,
    'post_type' => 'product',
    'orderby' => 'date');
    $loop = new WP_Query($args);
    while ($loop->have_posts()) : $loop->the_post();
        global $product; ?>         
        
        <?php
        $productID = get_the_ID(); 
        
        
        ?>
        <div class="column">
            <a href="<?php echo get_permalink($loop->post->ID) ?>">
                <?php the_post_thumbnail('full'); ?>
            </a>
            <?php 
            $target = get_field( 'campaign_target_amount' );
            
            ?>
            
            <div class="progress" style="margin-top:20px;">
              <div class="progress-bar progress-bar-warning progress-bar-striped active" role="progressbar"
              aria-valuenow="40" aria-valuemin="0" aria-valuemax="100" style="width:40%">
                40%
              </div>
            </div>
            
            <div class="row">
                <div class="col-md-8" style="padding-top:7px;">
                    Target : <b style="font-size: 18px;">RM <?php echo $target;?></b>
                </div>
                <div class="col-md-4">
                    <a class="btn" href="<?php echo get_permalink($loop->post->ID) ?>">Donate Now</a>
                </div>
            </div>
        </div>  
    <?php endwhile; ?>
    <?php wp_reset_query(); ?>
      </div>

       <?php
    }
    add_shortcode('loop_ffh', 'loop_ffh_function');

How to get a sum amount of sales for each product and display it in the loop?

Answer

You can use the following custom functions that will get for each product, the net revenue or/and the gross revenue:

function get_product_net_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(product_net_revenue)
        FROM {$wpdb->prefix}wc_order_product_lookup
        WHERE product_id = %d
    ",$product_id ) );
}

function get_product_gross_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(product_gross_revenue)
        FROM {$wpdb->prefix}wc_order_product_lookup
        WHERE product_id = %d
    ",$product_id ) );
}

Code goes in functions.php file of the active child theme (or active theme).
Tested and works in WooCommerce 4 and above.

Example usage inside your loop:

<?php $amount = get_product_net_revenue( $loop->post->ID ); ?>

Then you can use this float amount variable where you need.


Addition:

To get the net revenue only for “completed” orders use:

function get_product_net_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(o.product_net_revenue) 
        FROM {$wpdb->prefix}wc_order_product_lookup o 
        INNER JOIN {$wpdb->prefix}posts p
            ON o.order_id = p.ID
        WHERE p.status = 'wc-completed'
            AND o.product_id = %d
    ",$product_id ) );
}

To target “processing” and “completed” orders replace:

WHERE p.status = 'wc-completed'

with

WHERE p.status IN ('wc-processing','wc-completed')