[SQL] Aggregate function on joined tables

[Ruslan Zasukhin]

on 9/12/03 4:18 AM, David Hood at david.hood at stonebow.otago.ac.nz wrote:

> 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.

Hi David,

Seems to be true.
We need take care to make this work in 2.0


Ivan, please add SQL test which consider such case.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------