mercredi 29 juillet 2015

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.

Aucun commentaire:

Enregistrer un commentaire