MySQL Forums
Forum List  »  Newbie

Re: Unnexpected nonaggregated field error
Posted by: Rick James
Date: October 17, 2016 04:17PM

For readability:

    
    SELECT  Page,
            SUM(GivenNames != '') AS namecount,
            SUM(Age != '') AS agecount,
            SUM(IDIR != 0) AS idircount,
            PT_Population,                 -- Expression #5
            PT_Transcriber,
            PT_Proofreader
        FROM  Census1911
        JOIN  Pages  ON PT_Census='CA1911'
          AND  PT_DistId=District
          AND  PT_SdId=SubDistrict
          AND  PT_Div=Division
          AND  PT_Sched='1'
          AND  PT_Page=Page
        WHERE  District=66
          AND  SubDistrict='14'
          AND  Division=''
        GROUP BY  Page
        ORDER BY  Page

Array ( [0] => 42000 [1] => 1055 [2] => Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jcobban_genealogy.Pages.PT_Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When doing a GROUP BY, you should (must in the case of "only_full_group_by") include all the non-aggregate thingies (SUM is an aggregate function) in the GROUP BY list.

Without including PT_Population (etc) in the GROUP BY, you cannot predict what you will get.

Perhaps this is a "groupwise max" problem? If so, see
https://mariadb.com/kb/en/mariadb/groupwise-max-in-mariadb

Options: ReplyQuote


Subject
Written By
Posted
Re: Unnexpected nonaggregated field error
October 17, 2016 04:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.