title: SQLite - How to perform COUNT() with a WHERE condition?
body: <p>I have a <code>products</code> table with these fields: <code>_id</code>, <code>product_name</code>, <code>priority</code> and <code>shelf_id</code>.</p>
  <p>And I have a <code>shelves</code> table with these fields: <code>_id</code> and <code>shelf_name</code>.</p>
  <p>Currently, I have this SQL which returns a resultset showing the name of each shelf along with the number of products within each shelf:</p>
  <pre><code>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) &gt; 0
      
  ORDER BY
      shelf_name ASC
  </code></pre>
  <p>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 <code>priority</code> value that is greater than zero. Something along the lines of...</p>
  <pre><code>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 &gt; 0) AS num_products_in_shelf_with_priority
  ...
  </code></pre>
  <p>...but valid, of course.</p>
  <p>I have searched for <strong>sqlite subqueries</strong> and found <a href="https://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm" rel="nofollow noreferrer">this tutorial</a>, but it doesn't seem to be what I'm after.</p>
  <p>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 <code>num_products_in_shelf_with_priority</code> data.</p>