Jump to content

ExecuteSQL: How do I sort my columns by count value, but only display the value in the results?


Recommended Posts

  • Newbies
I have an ExecuteSQL question that I'm hoping you could help solve for me [FMP18A on MAC].

The best way for me to outline my question is to show the process I took to get to where I am currently.

[Example data only]

I have the following TABLE [Sales_Database] and COLUMN [FRUIT] setup, and my SQL calculation gives the following result:

------------------

ExecuteSQL (

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

"

; "" ; "")

 

Apple, 77

Oranges, 67

Grapes, 57

Pears, 47

Bananas, 37

------------------

I then broke it down further, wanting the results to only display the top most value (Apple). So, my SQL calculation and result was thus:

------------------

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

FETCH FIRST 1 ROWS ONLY

"

; "" ; "")

 

Apple, 77

------------------

 

So far, so good.

 

HOWEVER, for the purpose of using the result in a HIDE OBJECT WHEN calculation, I need the result to only show the word "Apple" no number attached.

 

Removing the COUNT(*) just results in the calculation ordering the values by the length of their letters.

 

I hope there's a solution to this problem, and thank you all in advance.

Link to post
Share on other sites

Don't forget the other functions available to you. You could set the ExecuteSQL field separator to a carriage return, or take your result and use Substitute -- and then get the first value, so the whole thing would be:

GetValue( Substitute(result;", ";¶) ; 1 )

That said, I would highly advise you not to use ExecuteSQL in any hide condition, tooltip, conditional format, etc. In a hosted, multi-user file, an uncommitted record can cause the client to attempt to retrieve the entire database. Performance will suffer to say the least.

Welcome to the forums!

Link to post
Share on other sites
  • Newbies

Hi Fitch, thank you for your reply.

Would you mind elaborating a little on how I implement the "GetValue( Substitute(result;", ";¶) ; 1 )"

Does it goes at the end of the SQL query, or elsewhere?

Link to post
Share on other sites

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

 

58 minutes ago, Fitch said:

I would highly advise you not to use ExecuteSQL ...

I agree. I would suggest a native alternative, but I am not sure what is the exact purpose of this exercise.

 

Link to post
Share on other sites
  • Newbies
33 minutes ago, comment said:

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

Apologies, I had no idea that both these websites were connected.

I'll continue this discussion in the other thread.

Appreciate all your help with this.

Link to post
Share on other sites
41 minutes ago, Jay86 said:

I had no idea that both these websites were connected.

They are not, but some of us read both. You are welcome to post on both, but a heads up would be appreciated in order to save duplication of effort.

 

Link to post
Share on other sites

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.