How to calculate Feed Conversion Ratio?

Question

I am trying to create a SQL query to calculate the feed conversion ratio of animals. Both TotalFeedConsumed and FCR columns are returning incorrect values. I want to know how to do this when applying the formula for calculating feed conversion ratio(FCR).

FCR = Total Feed Consumed/Total Weight Gained

See code that I had tried below

SELECT 
  a.Birthdate,
  a.Tagno,  
  a.Animal,
  SUM(f.Amount) AS TotalFeedConsumed,      
  CASE WHEN (MAX(w.Weight) - MIN(w.Weight)) > 0 THEN SUM(f.Amount) / (MAX(w.Weight) - MIN(w.Weight))  ELSE NULL END AS FCR
FROM Animals a
LEFT JOIN Animalfeeding f ON a.Tagno = f.Tagno
LEFT JOIN AnimalMeasure w ON a.Tagno = w.Tagno
GROUP BY a.Birthdate, a.Tagno, a.Animal ORDER BY FCR

Animal table

Tagno	Animal	Birthdate
809	Cow	02/11/2024
0155	Horse	11/01/2025
74	Goat	04/02/2025
44	Cow	22/03/2025
35	Busu	29/03/2025

AnimalMeasure table

Date	Tagno	Animal	Weight(kg)
20/02/2025	809	Cow	25.00
14/02/2025	809	Cow	20.00
06/02/2025	809	Cow	10.00
03/01/2025	0155	Horse	35.00
01/01/2025	0155	Horse	30.00

Animalfeeding table

Birthdate	Tagno	Animal	Feed Details	Amount
19/05/2025	809	Cow	Soya Meal	10.00
15/05/2025	809	Cow	Beans Meal	5.00
03/01/2025	0155	Horse	Wheat bran	4.00
01/01/2025	0155	Horse	Wheat bran	4.00

Expected output

Tagno	Animal	Birthdate	TotalFeedConsumed(kg)	FCR
809	Cow	02/11/2024	15.00	1.0
0155	Horse	11/01/2025	8.00	1.6
74	Goat	04/02/2025	0	0
44	Cow	22/03/2025	0	0
35	Busu	29/03/2025	0	0

Answer 1

You need to pre-aggregate your joins, otherwise you have a cartesian product between every Feed and every Measure for each Animal.

SELECT 
  a.Birthdate,
  a.Tagno,  
  a.Animal,
  f.TotalFeedConsumed,      
  CASE WHEN w.MaxWeight - w.MinWeight > 0 THEN
    f.TotalFeedConsumed / (w.MaxWeight - w.MinWeight)
  END AS FCR
FROM Animals a
LEFT JOIN (
    SELECT
      f.Tagno,
      SUM(f.Amount) AS TotalFeedConsumed
    FROM Animalfeeding f
    GROUP BY
      f.Tagno
) f ON a.Tagno = f.Tagno
LEFT JOIN (
    SELECT
      w.Tagno,
      MIN(w.Weight) AS MinWeight,
      MAX(w.Weight) AS MaxWeight
    FROM AnimalMeasure w
    GROUP BY
      w.Tagno
) w ON a.Tagno = w.Tagno
ORDER BY FCR;

db<>fiddle

On a side note: your database is denormalized. You should only store Animal attributes in a single table. The other child tables should only have a foreign key to it, so you shouldn't have Animal and Birthdate columns.

I hope this isn't being used in a real-life feed usage calculation, because you'd ideally want to do more complex calculations such as weight gained per each measure, and then average that. The max weight isn't necessarily the most accurate datapoint for that.