Woocommerce Question posted in
The official Woocommerce documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

    While @LoicTheAztec’s answer already covers it comprehensively; there are times when you just need to know the total sold quantity (such as when the price is fixed), and in those cases you can replace the query with one of the following:

    SELECT total_sales
    FROM {$wpdb->prefix}wc_product_meta_lookup
    WHERE product_id = %d

    or

    SELECT meta_value
    FROM {$wpdb->prefix}post_meta
    WHERE post_id = %d
      AND meta_key LIKE 'total_sales'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search