CODEWITHSUNDEEP
sqlsqlitesubquery
Boycott A.I.
Boycott A.I.

Reputation: 18891

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.

Upvotes: 13

Views: 20611

Answers (4)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 21

NearHuscarl
NearHuscarl

Reputation: 81490

In SQLite v3.32.0 and above, you can use IIF() function to count only products with priority larger than 0:

SUM(IIF(products.priority > 0, 1, 0))

Another alternative using COUNT():

COUNT(IIF(products.priority > 0, 1, NULL))

Upvotes: 9

blubr
blubr

Reputation: 122

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_priority
FROM shelves a
ORDER 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.

Upvotes: 3

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31173

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

Upvotes: 3

Related Questions