Dashboard Statistics Fix (PostgreSQL)

Root Cause

Joining animals -> treatments -> weights in one query fans rows out. An animal with 3 treatments and 2 weights yields 6 joined rows, so COUNT(*) and AVG(weight) become wrong. Example: the farm has 100 animals, the dashboard reports 247.

COUNT(DISTINCT a.id) fixes the animal count but not the average, because each weight record is still averaged once per joined treatment row.

Correct Metrics

Values verified against a 100-animal sample with 180 treatments and 260 weight records.

The Fix: Aggregate at Each Metric's Own Grain

WITH latest_weights AS (
    SELECT DISTINCT ON (animal_id) animal_id, weight_kg
    FROM weights
    ORDER BY animal_id, recorded_at DESC
)
SELECT
    (SELECT COUNT(*) FROM animals)                       AS total_animals,
    (SELECT COUNT(DISTINCT animal_id) FROM treatments)   AS treated_animals,
    (SELECT ROUND(AVG(weight_kg), 2) FROM latest_weights) AS avg_latest_weight;

Before / After