Unnexpected nonaggregated field error
Running with Server version: 5.7.15-0ubuntu0.16.04.1 (Ubuntu) I am getting the following error message, and I cannot figure out how to get around the condition that the server is complaining about without turning off the sql_mode.
SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount, PT_Population, 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
The JOIN for table Pages identifies every part of the unique key of the table:
CREATE TABLE `Pages` (
`PT_Census` VARCHAR(6) NOT NULL DEFAULT 'CA1881',
`PT_DistId` DECIMAL(4,
1) NOT NULL,
`PT_SdId` VARCHAR(5) NOT NULL,
`PT_Div` VARCHAR(4) NOT NULL DEFAULT '',
`PT_Sched` CHAR(1) NOT NULL DEFAULT '1',
`PT_Page` INT(4) NOT NULL DEFAULT '0',
`PT_Population` INT(2) DEFAULT '25',
`PT_Image` VARCHAR(128) DEFAULT '',
`PT_Transcriber` VARCHAR(64) DEFAULT '',
`PT_ProofReader` VARCHAR(64) DEFAULT '',
PRIMARY KEY (`PT_Census`,
`PT_DistId`,
`PT_SdId`,
`PT_Div`,
`PT_Sched`,
`PT_Page`),
KEY `PT_Image` (`PT_Image`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
I am grouping the response from the first table `Census1911` on Page, and all of the fields in the primary key of Pages are specified in the JOIN therefore a single record from Pages should be selected for each record created from the first table so I do not understand why PT_Population should be considered "nonaggregated". I do not understand what I am expected to add to the GROUP BY to satisfy the server.