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:
x
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;