FM 12 ExecuteSQL statement not working...seems simple...

[Justin Close]

Can someone help me figure out why this, apparently, simple ExecuteSQL statement doesn't work?

The short description is that we are trying to count the occurrence of words. The table has some 4100+ records, each one (the "Text" field) simply a word from a document. I was trying to test if putting a numeric identifier for a word, instead of the text word itself, would be faster. (The text based 'Group By...' takes 25 seconds!!) So I have been trying to test whether different techniques will help (indexing, this numeric field thing, etc). Here's a sample of my data:

ExecuteSQL (

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY GroupNum"

;"";""

)

While this version does (only removed the "Text" field from the Select):

ExecuteSQL (

"SELECT GroupNum, Count (GroupNum)

FROM Test

GROUP BY GroupNum"

;"";""

)

I have a very simple table structure as I was just doing some SQL testing.  See attached image cap.

availability    96

availability    96

available    97

average    98

awake    99

aware    100

aware    100

away    101

back    102

back    102

bag    103

balance    104

ball    105

barely    106

barometer    107

barrel    108

barrel    108

barrels   109 

So, my SQL is just designed to give me the resulting count of words, in one field ("Results"). So I wanted to make it prettier by including the "Text" word as well as the numeric version.

Thanks,

J

[Lem Alpha]

Hi there,

when using an aggregate function (such as COUNT, SUM...), you MUST put all the other columns (ie that are not aggregated) in the GROUP BY.

Therefore, try :

ExecuteSQL(

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY Text, GroupNum"

;"";""

)

[Ocean West]

Automatic message

This topic has been moved from "Community Resources → FileMaker Pro 12" to "Database Schema & Business Logic → FQL - Internal SQL".

[Justin Close]

Hi there,

when using an aggregate function (such as COUNT, SUM...), you MUST put all the other columns (ie that are not aggregated) in the GROUP BY.

Therefore, try :

ExecuteSQL(

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY Text, GroupNum"

;"";""

)

Laurent,

Thanks for the tip. It seems like an odd requirement, however. But, it did work...to an extent. Unfortunately, it is now doing TWO Group By operations, and so now takes 38 seconds instead of 19. However, that is still slightly faster than doing the SQL against the Text field directly (about 22 seconds for that one).

Thanks,

J

[mr_vodka]

Are these fields indexed?

[Justin Close]

I tried it both ways and didn't see a change in performance. :(

[mr_vodka]

How many records are we talking about? 22 seconds seems awfully long for that. I have a layout with about 100 separate calls that groups and sums, the largest data sets take at most 15 seconds.

[Justin Close]

About 4100 records. Simple structure just to do some of this ExecuteSQL testing: one table (no T.O.s), 7 or so fields...posted a pic of the table back a few messages.

[Wim Decorte]

"Text" is probably a reserved keyword so I would try to rename the field and try again with the original query.

[Lee Smith]

Good call, it is. for a full list, SQL Reserved Words