GROUP BY in ExecuteSQL

[abross86]

I can't seem to get any ExecuteSQL calculation working that used GROUP BY. Here is a very simple example that I just tried. It wont work unless I take out the GROUP BY statement:

ExecuteSQL ("

SELECT * FROM citystatezip
WHERE city='Wilmington'
ORDER BY citystatezip_id
GROUP BY zip

";",";¶)

I've tried everything I can think of, indexing all the fields, rearranging the order by and group by statements, etc. Again, it works perfect as soon as I take out the 'GROUP BY zip' line

[beverly]

GROUP BY is improperly used here. You don't have any summaries to group.

[comment]

It sounds like you want to do something like:

SELECT DISTINCT zip
FROM citystatezip
WHERE city='Wilmington'

 

[abross86]

I guess my question is more generally about how to get GROUP BY to work in ExecuteSQL. For example, I can run this query fine on a mysql version of the database from a mysql client:

SELECT MAX(citystatezip_id),city,zip FROM citystatezip
WHERE city='Wilmington'
GROUP BY zip
ORDER BY citystatezip_id

...but when I run it on the filemaker version using executesql, I just get "?". The actual queries im trying to run are more complex, I just made the above query as a test. Is the filemaker sql syntax significantly different from mysql?

[comment]

Is the filemaker sql syntax significantly different from mysql?

 

I don't know. I suggest you have a look at the FileMaker 13 SQL Reference document (@page 11)  to get at least a hint (if not an actual definition) of how this is supposed to work.

[Brian C]

The Max function is designed to return a summarized (aggregate) result for multiple records. Unfortunately FileMaker only allows you to return one column of data in addition to the summary function you choose wether it be SUM, MAX, etc. So this works:

 

SELECT MAX(CityStateZip_ID),zip

FROM CityStateZip

WHERE city='Wilmington'

 

But this does not:

 

SELECT MAX(CityStateZip_ID),City,State,Zip

FROM CityStateZip

WHERE city='Wilmington'

 

Similarly GROUP BY is also used with summarized data (aggregates).

It is important to note that according to the documentation, you cannot reference a column in GROUP BY without first referencing it in the SELECT. So this works:

 

SELECT Zip,MAX(CityStateZip_ID)

FROM CityStateZip

WHERE city='Wilmington'

GROUP BY Zip

 

But this does not because the column Zip is not referenced in the SELECT first:

 

SELECT City,MAX(CityStateZip_ID)

FROM CityStateZip

WHERE city='Wilmington'

GROUP BY Zip 

 

ORDER BY won't work in the same query as GROUP BY (At least in FileMaker) because GROUP BY is designed to work on summarized data (aggregates). As a work around I recommend you store your MAX functionality as an unstored calculation field in your CityStateZip table so that the result can be pulled in the query as a standard column.  If you create a relationship based on the City Name to a duplicate TO of the CityStateZip TO, you can just use max(CityStateZip_MAX::CityStateZip_ID)  in the unstored calculation field.  What you want to use the GROUP BY for can be done within the ORDER BY to some extent.  Your query Was:

 

SELECT MAX(citystatezip_id),city,zip

FROM citystatezip
WHERE city='Wilmington'
GROUP BY zip
ORDER BY citystatezip_id

 

Which if rewritten to a close approximation, would be:

 

SELECT MaxCityState_ID_c,City,State,Zip,

FROM CityStateZip

WHERE city='Wilmington'

ORDER BY Zip,CityStateZip_id

 

And the results returned look like this:

 

42048,WILMINGTON,MA,01887

42048,WILMINGTON,VT,05363

42048,WILMINGTON,NY,12997

42048,WILMINGTON,DE,19801

42048,WILMINGTON,DE,19802

42048,WILMINGTON,DE,19803

42048,WILMINGTON,DE,19804

42048,WILMINGTON,DE,19805

 

This is probably not exactly what you are looking for but I believe that you can probably make use of INNER JOIN to get to the result you want. I tried using DISTINCT but it does not work well with multiple columns that fall outside of the DISTINCT function. To summarize, MySQL seems to be a lot more flexible with SQL functions beyond their original intent. This flexibility is great, but FMSQL is not quite there yet. On the plus side FileMaker does at least give us the tools to still be able solve the problem in other ways.

Edited June 19, 2014 by Brian C

[the Otter]

Sorry to resurrect an old thread, but I’ve just discovered that FileMaker 13 does allow you to ORDER BY the same field as GROUP BY. So, this should work:

SELECT MAX(citystatezip_id),zip 

FROM citystatezip
WHERE city='Wilmington'
GROUP BY zip
ORDER BY zip

 

Just FYI….

Edited June 30, 2015 by the Otter

[Diver]

After reading this thread I believe the GROUP BY would give me what I want but I believe my syntax is wrong and thus I am getting a '?' returned.  My actual calc uses the 'Let' function for things but I tried to simplify things for this post.

I have a list of invoice that repeat and I only want one of each AND the UUID for each of them.  As example my data looks like this:

64320    C3990C50-92E9-1F4B-A3AF-3EE9329FBD04

64320    D2B65E90-A187-C043-B454-EDBEFDF32EB1

64320    D98BF80C-E1A9-0044-93C2-6F12F5713FE1

64565    0042E320-F937-E145-BF22-DD9792E4FA82

64565    05A930D5-B2BD-7340-8182-6F40F216F858

64565    0670C290-E938-4246-A5D6-2C6470A3FFB5

64565    2CAB9CEC-FD22-634C-ADAE-44220EB3C0CC

I want the result of my sql to be like this:

64320    C3990C50-92E9-1F4B-A3AF-3EE9329FBD04

64565    0042E320-F937-E145-BF22-DD9792E4FA82

I really don't care which of column 2 is returned for any given invoice (column 1), I just need distinct column's 1 with the column 2.

When I try using the DISTINCT function it considers every record unique because the column 2 is always different.

This works for getting the whole list:

Let(

vResult=ExecuteSQL (   

 "SELECT  \"Invoice\", \"UUID\"   

 FROM \"Sales\" a     

WHERE a.\"ItemID\" = ? " ;   

 "    " ; "|*|" ;   

 12345      

) ;

Substitute ( vResult ; "|*|" ; ¶ )

)

As soon as I try doing something like this, I get a '?':

 

Let(

vResult=ExecuteSQL (   

 "SELECT  \"Invoice\", MAX(\"UUID\" ) 

 FROM \"Sales\" a     

GROUP BY "\Invoice\"

WHERE a.\"ItemID\" = ? " ;   

 "    " ; "|*|" ;   

 12345      

) ;

Substitute ( vResult ; "|*|" ; ¶ )

)

Even if I take out the 'GROUP BY' line, I get an error, meaning there is something wrong with my 'SELECT' line.   Until I fix that I can't tell if my 'GROUP BY' will give me the result I want.

Any suggestions on what I have wrong?

[comment]

20 hours ago, Diver said:

I really don't care which of column 2 is returned

Then why do you need to return any value from column 2? You could simply return distinct values from column 1 only.

Alternatively return MIN() or MAX() of column 2 and group by column 1.

 

[Diver]

This portal is going to show summary data in several other columns.   Thus I need to identify each unique invoice and I will then use the uuid to get summary data.  I believe my logic is correct in what I am trying to do, my syntax is just not correct.  As noted above, I get an error when I try to do a MAX(), even with out the group by.

This might explain it better, the reason I can't just grab the invoice number is the sql result is being used as a portal filter.  If I grab the invoice number I will get all duplicate invoices.  I just want one invoice per portal row.   I am unable to use the relationship graph to filter in this unique case because of some of the data I am displaying in the portal would break.

[comment]

I am afraid you have lost me at this point. This thread is about the ExecuteSQL() function, so I don't see what a portal has to do with this.

The result specified in your post can be easily achieved using the following query:

SELECT Invoice, MIN (UUID)
FROM Sales
GROUP BY Invoice

as shown in the attached screenshot. 

 

And since you say you don't care about the UUID values, you could do simply:

ExecuteSQL ("SELECT DISTINCT Invoice FROM Sales " ; "" ; "" )

and get the result of:

64320
64565

 

If you want something else, then you need to clarify - preferably in a new thread.