Re: Inserting a MySQL select result not working? part 2

[Amanda J]

Thanks!

but got the syntax error again?

Is the SET SESSION group_concat_max_len = 20480 in the wrong place?

"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SESSION group_concat_max_len = 20480' at line 22"

INSERT INTO `fao_avg_cc`
(
species_code_c,
year_c,
n_years,
id_used,
avg_taken
)
SELECT
x.species_code_c,
x.year_c,
COUNT(*) AS n_years,
GROUP_CONCAT(DISTINCT x.capture_id) AS id_used,
AVG(y.qty_taken) AS `avg_taken`
FROM fao_capture AS x
JOIN fao_capture AS y
ON x.species_code_c = y.species_code_c
AND x.year_c BETWEEN y.year_c AND y.year_c + 5
AND x.year_c > 0
AND x.species_code_c <> NULL
GROUP BY x.species_code_c, x.year_c DESC
SET SESSION group_concat_max_len = 20480;