Jump to content

GROUP BY in ExecuteSQL


This topic is 2463 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Brian C
  • Like 1
Link to comment
Share on other sites

  • 1 year later...

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 by the Otter
Link to comment
Share on other sites

  • 2 years later...

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?

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

screen.thumb.png.617257ef1c0f7c5112d7885304ba7cf4.png

 

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.

 

 

 

 

  • Like 1
Link to comment
Share on other sites

This topic is 2463 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.