SQLite – How to perform COUNT() with a WHERE condition?

I have a products table with these fields: _id, product_name, priority and shelf_id.

And I have a shelves table with these fields: _id and shelf_name.

Currently, I have this SQL which returns a resultset showing the name of each shelf along with the number of products within each shelf:

SELECT
    shelves._id AS _id,
    shelves.shelf_name AS shelf_name,
    COUNT(products._id) AS total_num_products_in_shelf
    
FROM
    shelves

INNER JOIN
    products ON shelves._id = products.shelf_id
    
GROUP BY
    shelves._id
    
HAVING
    COUNT(products._id) > 0
    
ORDER BY
    shelf_name ASC

What I am trying to achieve is the creation of an additional column in the resultset that will show the number of products in each shelf that have a priority value that is greater than zero. Something along the lines of…

SELECT
    shelves._id AS _id,
    shelves.shelf_name AS shelf_name,
    COUNT(products._id) AS total_num_products_in_shelf,
    COUNT(products._id WHERE products.priority > 0) AS num_products_in_shelf_with_priority
...

…but valid, of course.

I have searched for sqlite subqueries and found this tutorial, but it doesn’t seem to be what I’m after.

Could someone help me out with a push in the right direction or, even better, modify my SQL query so that it will return valid num_products_in_shelf_with_priority data.

Answer

SELECT
shelves._id AS _id,
shelves.shelf_name AS shelf_name,
COUNT(products._id) AS total_num_products_in_shelf,
sum(case when products.priority > 0 Then 1 else 0 end) 
as num_products_in_shelf_with_priority
FROM shelves INNER JOIN products
ON shelves._id = products.shelf_id
GROUP BY shelves._id, shelves.shelf_name
HAVING COUNT(products._id) > 0
ORDER BY shelf_name ASC

You can include a case condition and then sum it. Also, included is the shelf_name in the group by clause.