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

SELECTshelves._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_priorityFROM shelves INNER JOIN productsON shelves._id = products.shelf_idGROUP BY shelves._id, shelves.shelf_nameHAVING COUNT(products._id) > 0ORDER 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.

Since others have shown how to do this using SUM, I'll show it using COUNT. I'm guessing the products table is separate from the shelves table, meaning you need some kind of a join or something.

SELECT shelves._id AS _id      ,shelves.shelf_name AS shelf_name      ,(SELECT COUNT(products._id) FROM products b WHERE a._id = b.shelf_id) total_num_products_in_shelf      ,(SELECT COUNT(products._id) FROM products b WHERE a._id = b.shelf_id AND b.priority > 0) AS num_products_in_shelf_with_priorityFROM shelves aORDER BY a.shelf_name ASC

If there are multiple rows per product id or something, change COUNT to COUNT(DISTINCT. If there are multiple rows per shelf then change SELECT to SELECT DISTINCT. Hope that helps.

You can use a sum and a condition for this:

SUM(CASE WHEN products.priority > 0 THEN 1 ELSE 0 END) AS num_products_in_shelf_with_priority