Re: Inserting a MySQL select result not working?

[Peter Brawley]

Your full outer join of fao_capture and fao_aquaculture returns four rows from your sample data, is that what you expect?

Your fao_global dataset has a blank line in the middle, on import throws 1762 warnings, some about too many or too few values per row, many about null values, I'll ignore those issues unless something indicates I shouldn't.

Quote

Using the SQL group operation AVG(...) did not seem to work. The query was returning the qty value. No average was being calculated?
UPDATE fao_global AS x
INNER JOIN
(
SELECT AVG(y.fao_total) as avg_total
FROM fao_global AS x
JOIN fao_global AS y ON x.species_code_c = y.species_code_c
AND y.year_c BETWEEN x.year_c AND x.year_c - 5
) y
SET x.avg_total = y.avg_total;

Without a Group By clause, an aggregating call like AVG(), SUM() or COUNT(*) returns just one row. Also there are lots of missing values, year values of 0, duplicate years per species_c, &c. Here's a query that displays the subset you wish to base an update on ...

select                                      -- FOR A MEANINGFUL RESULT,
  x.species_code_c,                         -- MUST GROUP BY SPECIES ...
  x.year_c,                                 -- AND YEAR
  group_concat(y.year_c order by y.year_c desc) as years
from fao_global x
join fao_global y on x.species_code_c=y.species_code_c
                 and x.year_c > 0           -- OMIT BAD YEAR DATA
                 and x.year_c between y.year_c and y.year_c+5
where x.species_code_c <> 'NUL'             -- OMIT MISSING DATA
group by species_code_c, x.year_c;

Notice it has to Group By x.species and x.year to return meaningful results.

In the sample dataset it returns 188 rows, 174 of which have no year value except themselves. Presumably the real data isn't so sparse.

Here is a version of that query to return the desired averages ...

select                                      
  x.species_code_c,                          
  x.year_c,                                 
  count(*) as N,
  avg(y.fao_total) as 5yrAvg
from fao_global x
join fao_global y on x.species_code_c=y.species_code_c
                 and x.year_c > 0           
                 and x.year_c between y.year_c and y.year_c+5
where x.species_code_c <> 'NUL'             
group by species_code_c, x.year_c;

If you think this query is wrong, please offer corrections, otherwise let's see how it performs against the real data.