[SQLite3] Get Product Onhand Quantity

Question

Using sqlite3, I have two tables: products, orders. I want to know how many products are left in the shop.

SELECT pid,
       txt,
       price,
       qty-coalesce((SELECT SUM(qty) 
                       FROM ORDERS
                      WHERE pid=?),0)
  FROM PRODUCTS
 WHERE pid=?

This works if I select 1 product, I would like a list of all my products ?

Answer 1

SELECT 
    P.pid, P.txt, P.price, 
        P.qty - coalesce((SELECT sum(O.qty) FROM orders O WHERE O.pid = P.pid), 0) 
FROM products P

Answer 2

Try this:

SELECT
    pid,
    txt,
    price,
    qty-coalesce(
        (SELECT sum(qty)
         FROM orders
         WHERE orders.pid = products.pid),0)
FROM products

Answer 3

I recommend using:

   SELECT t.pid,
          t.txt,
          t.price,
          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
     FROM PRODUCTS t
LEFT JOIN (SELECT o.pid,
                  SUM(o.qty) 'qty_sold'
             FROM ORDERS o) qs ON qs."o.pid" = t.pid
    WHERE t.pid = ?

While it works, using correllated SELECT statements in the SELECT clause will have the worst performance because they are executing once for every row returned in your query.

IFNULL is preferrable to use in this case compared to COALESCE. COALESCE is intended for checking 2+ values for being null, giving a false impression when someone else reads your code. There isn't any inherent benefit - per the documentation, they are the same.

Reference: SQLite Core Functions