ExecuteSQL baby steps

[Craig Wall]

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!

[eos]

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)

[Craig Wall]

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)

[eos]

 

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.

[Craig Wall]

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.

 

Good to know! Thanks again.