I have a simple table structure holding records of reagent_bottles and reagent_aliquots.
Aliquots are created from bottles, and an aliquot record got a foreign key, bottle_id, to a bottles id.
To get the total volume of a particular reagent (counting both the volume in the bottles and the volume in the aliquots), the following query does the job, partly:
SELECT COALESCE(SUM(rb.volume), 0) AS bVolume, COALESCE(SUM(ra.volume), 0) AS aVolume FROM reagent_bottles AS rb LEFT JOIN reagent_aliquotes AS ra ON rb.id = ra.bottle_id WHERE reagent_id = 408;
returns for the above reagent id (408):
bVolume | aVolume 2| 6
Adding bVolume and aVolume, is the correct answer, i.e. 8.
Question is, how do I re-write the above query to create a new field, total_volume, summing bVolume and aVolume within the query?
I tried this:
SELECT COALESCE(SUM(rb.volume), 0) AS bVolume , COALESCE(SUM(ra.volume), 0) AS aVolume, SUM(bVolume + aVolume) AS total_volume FROM reagent_bottles AS rb LEFT JOIN reagent_aliquotes AS ra ON rb.id = ra.bottle_id WHERE reagent_id = 408;
but getting the error, Unknown column, bVolume in field list..!
Advertisement
Answer
Sum the original columns:
SELECT COALESCE(SUM(rb.volume), 0) AS bVolume , COALESCE(SUM(ra.volume), 0) AS aVolume, SUM(COALESCE(rb.volume, 0) + COALESCE(ra.volume, 0)) AS total_volume FROM reagent_bottles AS rb LEFT JOIN reagent_aliquotes AS ra ON rb.id = ra.bottle_id WHERE reagent_id = 408;