Jump to content

ExecuteSQL baby steps


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

Recommended Posts

For the first time ever I'm working with ExecuteSQL and I can sure appreciate how--moving forward--it needs to be at the heart of a lot of my projects.

 

But I'm still having challenges with exact syntax.

 

I am trying to do a simple summary count...no subcategories...just a grand total. I can successfully generate a list, but don't want the list of items--just the count.

 

So how do I transform my working syntax to generate a simple count? I've been dismayed that Google can't help me pull up any examples of this.

 

Here is what works for the list:

 

ExecuteSQL 
"
SELECT (SCalendarDate)
FROM CCALENDAR 
WHERE Category ='Scheduled Holiday'  
AND
ComingPast='Coming'
 
";
 "," ; ""  )

 

I've had no success trying to adapt this for a list, using COUNT and such. It seems like it ought to be so very easy, but I can't find the exact syntax!

Link to comment
Share on other sites

Here is what works for the list:

 

ExecuteSQL 
"
SELECT (SCalendarDate)
FROM CCALENDAR 
WHERE Category ='Scheduled Holiday'  
AND
ComingPast='Coming'
 
";
 "," ; ""  )

 

You could apply ValueCount() to the result of your query, but SQL has a COUNT() function, so try:

ExecuteSQL ( "
  SELECT COUNT ( * )
  FROM CCALENDAR 
  WHERE 
    Category = 'Scheduled Holiday' AND
    ComingPast = 'Coming'
  "; "" ; "" 
)

 

I've been dismayed that Google can't help me pull up any examples of this. […] I can't find the exact syntax!

 

http://www.w3schools.com/sql/sql_func_count.asp (first Google result for SQL count)

Link to comment
Share on other sites

You could apply ValueCount() to the result of your query, but SQL has a COUNT() function, so try:

ExecuteSQL ( "
  SELECT COUNT ( * )
  FROM CCALENDAR 
  WHERE 
    Category = 'Scheduled Holiday' AND
    ComingPast = 'Coming'
  "; "" ; "" 
)

Worked like a charm! I guess my error was in thinking I had to use a field in the opening function section. Thank you so much!

 

http://www.w3schools.com/sql/sql_func_count.asp (first Google result for SQL count)

Link to comment
Share on other sites

 

You don't have to, but you can use a field – then it'd work just like FileMaker Count(), i.e. count only records where this field is non-empty in the returned set (with WHERE) or all records.

Link to comment
Share on other sites

This topic is 2864 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
 Share

×
×
  • Create New...

Important Information

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