Group By - requires all Select fields

[rivet]

I thought I should post this.  It seems GROUP BY  in FMPs ExecuteSQL function requires all the SELECT fields to work properly where that is not the case with mySql. 

 

SELECT
province, city, COUNT(*)
FROM
data
WHERE
province in ( 'ON', 'BC')
GROUP BY
province, city

 

RESULT:

BC, Abbotsford, 3
BC, Burnaby, 2
BC, Castlegar, 1
BC, Chilliwack, 3

…

 

 

ca-500.zip

Edited June 28, 2015 by rivet

[mr_vodka]

Yeah this was in their SQL documentation albeit written cryptically.  Definitely limited compared to mySQL.

https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf#page=11

 

[beverly]

Yes, this is true. So a SELECT *, COUNT(*) would require you to list ALL fields that might be returned in the GROUP BY (ugh!) And another reason I don't like to use "*" in the aggregates or in the SELECT at all.