Barrera Barrera - 1 year ago 228
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download