Re: Unnexpected nonaggregated field error
I don't understand. There is no reason for the fields in Pages, those starting with PT_ to be aggregated since there is only one matching record in Pages for each record obtained from Census1911.
Further confusion arises because the server only complains about this when the first table is Census1911, not when it is any other table with the same structure as Census1911.
The declaration of Census1911, which fails, is:
CREATE TABLE `Census1911` (
`District` DECIMAL(4,1) NOT NULL,
`SubDistrict` DECIMAL(4,0) NOT NULL,
`Division` VARCHAR(4) CHARACTER SET latin1 NOT NULL DEFAULT '',
`Page` INT(11) NOT NULL,
`Line` INT(11) NOT NULL,
`Family` CHAR(6) CHARACTER SET latin1 NOT NULL,
`Surname` VARCHAR(120) DEFAULT NULL,
...
PRIMARY KEY (`District`,
`SubDistrict`,
`Division`,
`Page`,
`Line`),
KEY `Surname` (`Surname`),
KEY `SurnameSoundex` (`SurnameSoundex`),
KEY `GivenNames` (`GivenNames`),
KEY `Relation` (`Relation`),
KEY `BYear` (`BYear`),
KEY `BPlace` (`BPlace`),
KEY `Origin` (`Origin`),
KEY `Nationality` (`Nationality`),
KEY `Religion` (`Religion`),
KEY `Occupation` (`Occupation`),
KEY `IDIR` (`IDIR`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
and the declaration of Census1901, which works, is:
CREATE TABLE `Census1901` (
`District` DECIMAL(4,1) NOT NULL,
`SubDistrict` VARCHAR(4) NOT NULL,
`Division` VARCHAR(4) NOT NULL DEFAULT '',
`Page` INT(11) NOT NULL,
`Line` INT(11) NOT NULL,
`Family` CHAR(6) NOT NULL,
`Surname` VARCHAR(120) DEFAULT NULL,
....
PRIMARY KEY (`District`,
`SubDistrict`,
`Division`,
`Page`,
`Line`),
KEY `Surname` (`Surname`),
KEY `SurnameSoundex` (`SurnameSoundex`),
KEY `GivenNames` (`GivenNames`),
KEY `Relation` (`Relation`),
KEY `BYear` (`BYear`),
KEY `BPlace` (`BPlace`),
KEY `Origin` (`Origin`),
KEY `Nationality` (`Nationality`),
KEY `Religion` (`Religion`),
KEY `Occupation` (`Occupation`),
KEY `Religion_2` (`Religion`),
KEY `IDIR` (`IDIR`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8
And recall that Pages is declared as:
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
The only functional difference in the structure between Census1901 that works and Census1911 that doesn't work is that in Census1901 the key field SubDistrict is a character string, like its counterpart in the Pages table. In Census1911 this field is declared as DECIMAL so it will sort correctly. In the 1901 census the subdistrict identifiers were the letters of the alphabet, so the values sort correctly for that census. But for the 1911 census the administrators changed them to numbers, which would sort incorrectly if normal text collation was used, because 1 would be followed by 10, not by 2. But why would the fields from the Pages table all of a sudden by non-aggregated simply because the type of the key changed? And what could I possibly put into the GROUP BY to satisfy the server?
And the datatype change doesn't explain the issue because there are other tables (1906, 1916, and 1921) that have SubDistrict defined as Decimal, but which do not fail:
CREATE TABLE `Census1921` (
`District` DECIMAL(4,1) NOT NULL,
`SubDistrict` DECIMAL(4,0) NOT NULL,
`Division` VARCHAR(4) DEFAULT '',
`Page` INT(11) NOT NULL,
`Line` INT(11) NOT NULL,
`Family` CHAR(6) NOT NULL,
`Surname` VARCHAR(32) DEFAULT NULL,
....
PRIMARY KEY (`District`,
`SubDistrict`,
`Page`,
`Line`),
KEY `Surname` (`Surname`),
KEY `SurnameSoundex` (`SurnameSoundex`),
KEY `GivenNames` (`GivenNames`),
KEY `Relation` (`Relation`),
KEY `BYear` (`BYear`),
KEY `BPlace` (`BPlace`),
KEY `FathersBPlace` (`FathersBPlace`),
KEY `MothersBPlace` (`MothersBPlace`),
KEY `Origin` (`Origin`),
KEY `Nationality` (`Nationality`),
KEY `Religion` (`Religion`),
KEY `Occupation` (`Occupation`),
KEY `IDIR` (`IDIR`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8