You probably have a name in your SQL that needs to be enclosed in "Quotes". Best guess is the fields with # as SQL is much more picky about what characters can be used in field and table occurrence names. Many developers just quote everything:
ExecuteSQL ("select count (a.\"Indication\") from \"Training_Aids\" a JOIN \"Sessions\" b. on a.\"fk_Session_Serial#\" = b.\"pk_Session_Serial#\" where a.\"Indication\" = ? " ; "" ; "" ; 1)
Of course a simple typo that misnames something could also be at fault.
To use the count function instead of SQL, you'd need a relationship that only matches to the records you want to count such as this relationship:
Sessions::pk_Session_Serial# = Training_Aids|value1::fk_Session_Serial# AND
Sessions::constOne = Training_Aids|value1::Indication
constOne would be a calculation field that always evaluates as the number 1 and Training_Aids|value1 would be a new Tutorial: What are Table Occurrences? of the Training_Aids table.
Then Count ( Training_Aids|value1::fk_Session_Serial# ) would return your value.
If you only need to display the count and not use it in other calculations there are ways to set up one row filtered portal with a summary field to get the same selective count.
will return the desired count.