Re-using calculations in query

Question

I want to create a view with a small amount of logic in my database. It is to check for product availability. I've created a simplified schema as a SQL Fiddle.

However, I need to re-use a value in multiple columns. Here is what I would like to do:

SELECT
    product.id AS id,
    SUM(IFNULL(purchase_product.amount,0)) AS amountSold,
    IFNULL(product.amountAvailable-amountSold,~0) AS amountAvailable,
    amountAvailable>0 AS isAvailable,
FROM product
    LEFT JOIN purchase_product ON purchase_product.product_id = product.id
    LEFT JOIN purchase ON purchase.id = purchase_product.purchase_id AND purchase.completed = TRUE
GROUP BY product.id

But this fails because because I can't re-use "amountSold" and "amountAvailable". This gives me the choise of either repeating the calculation or using subqueries (/joining with SELECT's/using more views).

This is the most reasonable compromise I've been able to come up with is in the Fiddle. Is there a more graceful solution?

Answer 1

I quite like your current solution, I see no real issue with it, however I think this might be slightly simpler:

SELECT  Product.ID, 
        COALESCE(AmountSold, 0) AS AmountSold,
        GREATEST(COALESCE(AmountAvailable, ~0) - COALESCE(AmountSold, 0), 0) AS AmountAvailable,
        COALESCE(AmountAvailable, ~0) - COALESCE(AmountSold, 0) > 0 AS IsAvailable
FROM    Product
        LEFT JOIN 
        (   SELECT  Product_ID, SUM(Amount) AS AmountSold
            FROM    Purchase_Product
                    INNER JOIN Purchase
                        ON Purchase.ID = Purchase_ID
            WHERE   Completed = 1
            GROUP BY Product_ID
        ) sold
            ON Sold.Product_ID = Product.ID;

The only real change is I have removed a reference to product in the subquery to reduce the number of table scans, and also slightly changed the logic of the calculation.

Finally I added GREATEST in there, just to account for any data integrity issues there may so that you never have negative products available.

You could do further subqueries to add to readability of the query, but it will almost certainly not enhance performance.

SQL Fiddle shows the slightly improved execution plan.

Answer 2

You can do it in a subquery with a join:

select product.id, s.amountsold,
       IFNULL(product.amountAvailable-s.amountSold,~0) AS amountAvailable, 
       product.amountAvailable>0 AS isAvailable
       -- although you probably mean
       -- IFNULL(product.amountAvailable-s.amountSold,~0) > 0 as isAvailable
from product join
     (SELECT product.id AS id, 
             SUM(IFNULL(purchase_product.amount,0)) AS amountSold,
             IFNULL(product.amountAvailable-amountSold,~0) AS amountAvailable, 
             amountAvailable>0 AS isAvailable
      FROM product LEFT JOIN
            purchase_product
            ON purchase_product.product_id = product.id LEFT JOIN
            purchase
            ON purchase.id = purchase_product.purchase_id AND
               purchase.completed = TRUE
      GROUP BY product.id
    ) t
    on product.id = t.id

In other databases, you can do something similar using windows functions. However, mysql does not support them.

Also, I think you want the isAvailable to be after you subtract the sold, so I added that in as a suggestion.