how to write group-by query SQL with quantity unit conversion?

Stock

Product  Quantity  Unit 
-------------------------------
001       5        box
001       10       bottle
001       60       gallon


Conversion

Unit1     Unit2    Rate
----------------------
box       bottle    20
bottle    gallon    30

Unit1 = Unit2 * Rate

How to write SQL query to sum(quantity) in certain unit (such as bottle in the example)?

select product, sum(quantity * ....) from Stock group by product

can a database function such as getRate(unit1, unit2) do the job? how about the performance?

Answer

You need to expand the conversion table so it has all combinations. This is a one time effort, but you need to add rows like this:

Unit1     Unit2    Rate
----------------------
box       bottle    20
bottle    gallon    30
gallon    bottle    1/30

Then you can use a LEFT JOIN:

SELECT s.product, SUM(s.quantity * coalesce(c.rate, 1))
FROM stock s LEFT JOIN
     conversion c 
     ON s.unit = c.unit1 AND c.unit2 = 'bottle'
GROUP BY s.product