Select Count of Total Products as Well as Out of Stock Products from Table

Select count of total products as well as out of stock products from table

You'd use conditional aggregatiopn, i.e. sum/count over conditions:

select
  s.name,
  sum(p.quantity > 0) as in_stock,
  sum(p.quantity = 0) as out_of_stock,
  count(*) as total
from store s
join product_to_store ps on ps.store_id = s.id
join product p on p.id = ps.product_id
group by s.name
order by s.name;

This makes use of MySQL's true = 1, false = 0. If you don't like it, replace sum(p.quantity = 0) with sum(case when p.quantity = 0 then 1 else 0 end) or count(case when p.quantity = 0 then 1 end).

Set all products status "out of stock" based on orders count in Woocommerce

You can't make that in your shortcode directly… Also your function to get orders status count is a bit heavy. Instead you can use the following custom function that uses a very light SQL query to get the orders count.

Another set of hooked functions will set all products (even the product variations of the variable products) to "Out of stock" status, when the order count will reach 200.

Finally I have changed your shortcode function that will display the order count.

You can set the order statuses as you want, in the function that counts orders, in your shortcode too.

Instead use the following:

// Utility function to get orders count based on orders statuses 
// Default statuses are processing + completed (and you can change them as you like)
function get_orders_count_from_statuses( $statuses = 'processing, completed' ){
    global $wpdb;

    // Filtering order statuses
    $statuses = "wc-" . str_replace(array("wc-",", ",","), array("",",","','wc-"), $statuses );

    // return the query
    return (int) $wpdb->get_var("SELECT count(ID)  FROM {$wpdb->prefix}posts
    WHERE post_status IN ('$statuses') AND `post_type` LIKE 'shop_order'");
}


// Set conditionally product status based on order count limit
add_filter( 'woocommerce_product_get_stock_status', 'conditional_product_status', 10, 2 );
add_filter( 'woocommerce_product_variation_get_stock_status', 'conditional_product_status', 10, 2 );
function conditional_product_status( $stock_status, $product ){
    if( get_orders_count_from_statuses() >= 200 ){
        $stock_status = 'outofstock';
    }
    return $stock_status;
}


// Your shortcode function that will display the count (if needed)
add_shortcode( 'order_count', 'display_woocommerce_order_count' );
function display_woocommerce_order_count( $atts ) {
    $atts = shortcode_atts( array(
        'statuses' => 'processing,completed',
    ), $atts, 'order_count' );

    $order_count = get_orders_count_from_statuses( $atts['statuses'] );

    return number_format( $order_count ); // Always use return (not echo for a shortcode)
}

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

How to show count of products with category using SQL

First, you need to get all the sub categories related to the specific category. To do that, you use a recursive cte.

Then, you use a left join between that recursive cte and the products table, counting the products group by the root category.

Here's a code example:

First, create and populate sample tables (Please save us this step in your future questions):

DECLARE @Categories AS TABLE
(
    category_id int, 
    category_name varchar(50),   
    sub_category_id int
)
INSERT INTO @Categories (category_id, category_name, sub_category_id) VALUES
(1, 'Category_1'        , 0),
(2, 'Category_2'        , 0),
(3, 'Sub_Category_1'    , 1),
(4, 'Sub_Category_2'    , 2),
(5, 'Sub_Category_1_Sub', 3),
(6, 'Sub_Category_2_Sub', 4);

DECLARE @Products AS TABLE
(
    product_id  int,
    category_id int
)

-- comments to make it clear which product belongs which main category
INSERT INTO @Products (product_id, category_id) VALUES
(1, 3), -- Category_1
(2, 4), -- Category_2
(3, 3), -- Category_1
(4, 4), -- Category_2
(5, 5), -- Category_1
(6, 5); -- Category_1

Then, the recursive cte:

WITH CTE AS
(
    SELECT category_id, category_name, sub_category_id, category_name As MainCategoryName
    FROM @Categories 
    WHERE sub_category_id = 0
    -- If you want to start from a particular subCategory you can change the where condition:
    -- for instance, `where category_id = 3` will count the products the belongs to Sub_Category_1 and Sub_Category_1_Sub
    UNION ALL

    SELECT T.category_id, T.category_name, T.sub_category_id, MainCategoryName
    FROM @Categories AS T
    JOIN CTE
        ON T.sub_category_id = CTE.category_id
)

The query:

SELECT MainCategoryName As CategoryName, COUNT(P.product_id) As NumberOfProducts
FROM CTE
LEFT JOIN @Products As P
    ON P.category_id = CTE.category_id 
GROUP BY MainCategoryName 

Results:

CategoryName    NumberOfProducts
Category_1      4
Category_2      2

You can see a live demo on rextester.

SQL: Finding the total number of items for each category and ordering the result

Grouping by both Category and Industry:

select Category,Industry, count(*) as 'Number'
from Field
group by Category,Industry

Select with count by item and total count in SQL Server

Already answered in another post :
SUM of grouped COUNT in SQL Query

select name, COUNT(name) as count from Table
group by name
    Union all
select 'SUM', COUNT(name)
from Table

Get total of out of stock products, total price added to the inventory records for the user laravel

DB::table('itmes')
         ->select('business_id',DB::raw('count(*) as total_number, sum('price') as total_price'))
         ->where('quantity_in_stock', 0)
         ->groupBy('business_id')
         ->get();

SQL query to get number of products for each category

Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.

SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category 
ORDER BY nproducts ASC

Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.

How to get total sales for each product in Order Details table in Northwind database in a single statement?

You could just extract your sum function to sum the price for each row individually, instead of just summing the quantity (this should be OK mathematically due to the distributive property of multiplication over addition):

SELECT   productid,  SUM(UnitPrice * (1 - Discount) * Quantity)
FROM     [Order Details]
GROUP BY ProductID

Using SQL to find the total number times a product has been bought in one query

select ProductID, count(*) as NumSales from Orders group by ProductID

If there's a quantity field there too, try this:

select ProductID, sum(Quantity) as TotalQty from Orders group by ProductID

---

Related Topics