Barrera Barrera - 1 month ago 9
MySQL Question

Group by contains nonaggregated column

I'm trying to compute the average for each row in a table that has the same postcode and group it by that postcode and the year. I'm trying to run the following query

INSERT INTO processed_clean_properties (postcode,avgYearPostcodeNorm,latitude,longitude,yearSold)
SELECT postcode, round(avg(norm)), latitude, longitude, yearSold
FROM clean_properties
GROUP BY postcode, yearSold


and getting the following error

" Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forge.clean_properties.latitude' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

I've looked into it and tried to disable the only_full_group_by from the sql_mode but it doesn't seem t o save any if the server is restarted it resets to default.

I've also tried adding all the selected columns to the group by condition as such

INSERT INTO processed_clean_properties (postcode,avgYearPostcodeNorm,latitude,longitude,yearSold)
SELECT postcode, round(avg(norm)), latitude, longitude, yearSold
FROM clean_properties
GROUP BY postcode, norm, latitude, longitude, yearSold


Doing this makes the query run indefinitely without actually doing anything.

How can i correct the initial query to work with the full_group_by condition ?

Answer

If including latitude and longitude in the group by cause the query to run forever, then this will probably have the same effect:

INSERT INTO processed_clean_properties (postcode, avgYearPostcodeNorm, latitude, longitude, yearSold)
    SELECT postcode, round(avg(norm)),
           avg(latitude), avg(longitude), yearSold 
    FROM clean_properties 
    GROUP BY postcode, yearSold;

This gets the average of latitude and longitude for the rows for the postcode. This isn't exact, but it is probably no worse than just grabbing arbitrary values.