Try joining to the post_meta table using the _stock_status
meta_key where the meta_value is 'instock'. Caching the data is recommended since you don't want to run this on every request but will need to balance an appropriate amount of time to cache the data (since sales within the cache period won't be reflected in the total number of instock items). Only works if you are using a cache (which is highly recommended with WooCommerce due to the number of queries).
global $wpdb;
// cache key
$key = 'in_stock_products';
// we get back 'false' if there is nothing in the cache, otherwise 0+
$in_stock_products = wp_cache_get( $key );
// run the query if we didn't get it from the cache
if ( false === $in_stock_products ){
// create the SQL query (HEREDOC format)
$sql_query = <<<SQL
SELECT COUNT(p.ID) AS in_stock_products
FROM {$wpdb->posts} p
JOIN {$wpdb->postmeta} pm
ON p.ID = pm.post_id
AND pm.meta_key = '_stock_status'
AND pm.meta_value = 'instock'
WHERE p.post_type = 'product' AND p.post_status = 'publish'
SQL;
// query the database
$in_stock_products = (int) $wpdb->get_var( $sql_query );
// cache the results, choosing an appropriate amount of time to cache results
$cache_ttl = 0; // 0 is "as long as possible", otherwise cache time in seconds
wp_cache_add( $key, $in_stock_products, $cache_ttl ); // cache as long as possible
}
// $in_stock_products now has the value either from the cache or the database query.
echo "There are $in_stock_products in stock";
wp_postmeta
with field_sale_price
which is greator than0
– Rahil WazirSELECT COUNT(*) FROM $wpdb->posts p INNER JOIN $wpdb->postmeta pm ON p.id = pm.post_id WHERE p.post_type = 'product' AND p.post_status = 'publish' AND pm.meta_key = "_sale_price" AND pm.meta_value > 0
– Rahil Wazir