Get and display products On Sale count in WooCommerce

Question

By doing an SQL query (does not seem to work properly) which counts how many products that are on sale (it counts how many products that have discounted prices), my goal is to display a message on the main archive (shop) using a hook. The message should contain the count (how many products that are on sale).

Desired example output: "Looking for a great deal? We currently have XX products on sale!"

Any ideas or insight as to why this is not working? Here's my code:

function get_onsale_products_count() {
    global $wpdb;

    // SQL query for counting products that are on sale
    $result = $wpdb->get_col( "
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type LIKE '%product%'
        AND p.post_status LIKE 'publish'
        AND pm.meta_key LIKE '_stock_status'
        AND pm.meta_value LIKE '_on_sale'
    " );

    return reset($result);
}

add_action('woocommerce_before_single_product_summary', 'get_onsale_products_count');
$count = get_onsale_products_count();
$great_deal = sprintf( __( 'Want to make a good deal? We\'ve got %s products on sale!' ), $count );
echo '<span class="great-deal">'.$great_deal.'</span>';

Answer 1

Stock status has nothing to do with "on sale" products and _on_sale meta value doesn't exist, so that's why your SQL query doesn't work.

On sale products have a defined "sale price", So try instead:

function get_onsale_products_count() {
    global $wpdb;

    // SQL query for counting products that are on sale
    return $wpdb->get_var( "
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type LIKE '%product%'
        AND p.post_status = 'publish'
        AND pm.meta_key = '_sale_price'
        AND pm.meta_value != ''
    " );
}

add_action( 'woocommerce_before_single_product_summary', 'display_onsale_products_count_message', 3 );
function display_onsale_products_count_message(){
    echo '<span class="great-deal">' . sprintf( __( "Want to make a good deal? We've got %s products on sale!" ), 
    get_onsale_products_count() ) . '</span>';
}

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