SQL Select Max and Average

Environment is MySQL8. I have a table bird

+----+--------+--------+
| id |  name  |  sex   |
+----+--------+--------+
| 1  | bob    | male   |
| 2  | mary   | female |
| 3  | jono   | male   |
+----+--------+--------+ 

Bird is associated to Check

+----+-------------+---------+
| id | check_date  | bird_id |
+----+-------------+---------+
| 1  | 2023-06-01  |       2 |
| 2  | 2023-07-01  |       2 |
| 3  | 2023-08-01  |       2 |
+----+-------------+---------+

And each check can have several weights and measurements:

weights

+----+---------+----------+
| id | weight  | check_id | 
+----+---------+----------+
|  1 |    3.3  |        2 |
|  2 |    3.45 |        2 |
|  3 |    3.8  |        2 |
|  4 |    4.5  |        1 |
|  5 |    4.51 |        1 |
+----+---------+----------+

measurements

+----+---------+----------+
| id | measur  | check_id |
+----+---------+----------+
|  1 |    95.2 |        1 |
|  2 |    97.4 |        1 |
|  3 |    96.4 |        1 |
|  4 |    99.1 |        3 |
|  5 |    98.1 |        3 |
+----+---------+----------+

For a given bird how would I return a result set containing

+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
| bird.id | bird.name | newest check | date newest measurements | avg newest measurements | date newest weight | avg newest weight |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
|       2 | mary      | 2023-08-01   | 2023-08-01               |                    98.6 | 2023-07-01         |              3.52 |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+

I have attempted to write the query myself and got as far as :

SELECT bird.id, bird.name, MAX(check.catch_date), weight
FROM bird
LEFT JOIN check ON bird.id=check.bird_id
LEFT JOIN measurements ON measurements.check_id=check.id
WHERE bird.id=2
GROUP BY id, weight;

But the way to nest and average the result of measurements and length which could be associated to different checks is where I am stuck so narrative on how the answer SQL works would be helpful.

I recommend not using Keywords and Reserved Words such as check because you have to always use backticks.

Regarding the question (maybe there is a simple solution).

> But the way to nest and average the result of measurements and length
> which could be associated to different checks is where I am stuck

This can be done by join and the help of exist to retrieve the max check_id

select MAX(c.check_date) as date_newest_weight,
       ROUND(AVG(w.weight),2) as avg_newest_weight,
       c.bird_id
from  `check` c  
inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2))
where c.bird_id = 2 
group by c.bird_id;

Result

date_newest_weight	avg_newest_weight	bird_id
      2023-07-01	       3.91         	2

Final query

select b.id as bird_id,
       b.name as bird_name,
       max(c.check_date) as newest_check,
       date_newest_measurements,
       avg_newest_measurements,
       date_newest_weight,
       avg_newest_weight
from bird b 
inner join `check` c on b.id=c.bird_id
inner join ( select  MAX(c.check_date) as date_newest_measurements,
                     ROUND(AVG(m.measur),2) as avg_newest_measurements,
                     c.bird_id
             from  `check` c  
             inner join measurements m on c.id =(select max(m.check_id) from measurements m where exists (select id from `check` c where c.bird_id = 2)) 
             where c.bird_id = 2 
             group by c.bird_id
           ) as max_meas  on max_meas.bird_id=c.bird_id
inner join (
            select MAX(c.check_date) as date_newest_weight,
                   ROUND(AVG(w.weight),2) as avg_newest_weight,
                   c.bird_id
            from  `check` c  
            inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2))
            where c.bird_id = 2 
            group by c.bird_id
           )  as max_weig on max_weig.bird_id=c.bird_id
group by bird_id,
         bird_name,
         date_newest_measurements,
         avg_newest_measurements,
         date_newest_weight,
         avg_newest_weight;

Result

bird_id bird_name   newest_check    date_newest_measurements    avg_newest_measurements date_newest_weight  avg_newest_weight
  2      mary         2023-08-01         2023-08-01                    98.60                 2023-07-01             3.52

See example here