[SQL] Aggregate function on joined tables

[David Hood]

Tested on the current release of VAPP, I tried the SQL statement

"SELECT y1.OcGroup,  y2.OcGroup, COUNT(y2.OcGroup)  FROM y1,y2 WHERE 
y1.Year=1902 AND  y2.Year=1911 AND (y1.ID=y2.ID) GROUP BY  y1.OcGroup,  
y2.OcGroup ORDER BY y1.OcGroup,  y2.OcGroup"

but the resulting cursor contained no COUNT(y2.OcGroup) it only 
contains the grouped y1.OcGroup and  y2.OcGroup

If I change the name of field OcGroup in table y2 to OG and running the 
otherwise identical query

"SELECT y1.OcGroup,  y2.OG, COUNT(y2.OG)  FROM y1,y2 WHERE y1.Year=1902 
AND  y2.Year=1911 AND (y1.ID=y2.ID) GROUP BY  y1.OcGroup,  y2.OG ORDER 
BY y1.OcGroup,  y2.OG"

I get the correct result.

This suggests to me the current SQL parser doesn't like aggregates of 
same-named fields of different tables.



Regards,
David Hood

_/_/_/_/_/_/
David Hood, david.hood at stonebow.otago.ac.nz
Research Fellow,
The Caversham Project,
University of Otago,
Web: http://www.otago.ac.nz/nzpg/caversham