"get orders total purchases amount for the day in woocommerce" Answer’s


The best and effective way to get that is to use the following very light SQL query, that will get the sum of all order totals in the last 24 hours for "processing" and "completed" orders statuses:

function get_daily_purchases_total(){
    global $wpdb;

    return $wpdb->get_var( "
        SELECT SUM(pm.meta_value)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_order'
        AND p.post_status IN ('wc-processing','wc-completed')
        AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))
        AND pm.meta_key = '_order_total'
    " );

Code goes in function.php file of your active child theme (or active theme). Tested and works.

USAGE Example - Display the daily total purchased formatted amount:

<?php echo '<p>Total purchased of the day: ' . strip_tags( wc_price(get_daily_purchases_total() ) ) . '</p>'; ?>

If you want to get instead the total based on the "today" date, you will replace in the code this line:

AND UNIX_TIMESTAMP(p.post_date) >= (UNIX_TIMESTAMP(NOW()) - (86400))

by this line:

AND DATE(p.post_date) >= CURDATE()
Wednesday, March 31, 2021
answered 10 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :