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.
Aucun commentaire:
Enregistrer un commentaire