"Date & Time functions that work well in ExecuteSQL() and may be used with the System functions, above or your date fields or even the properly formatted Date/Time text (in single quotes ‘YYYY-MM-DD hh:mm:ss’):"

What type of result does the c_Resolved_Elapsed field return? FileMaker won't apply the SQL AVG function to a time field. FileMaker Pro 16 Advanced's Data Viewer would show the following error if that's the case:

Parameter number 1 to the function "AVG" is not of the correct type.

You could change the result type to a number and your query would likely work.

c_Resolved_Elapsed is a timestamp field. The funny thing is I have the average listed as a Leading Grand Summary on a table view I just want to put this average value in a field. But yeah its a timestamp

Check out RazorSQL as one example as it will do so much more than just get rid of the ridiculous and unhelpful ... ?

Often the error isn't at all what you thought.

Had you been using a real SQL Tool (or FMP 16), you would have seen the error right away and not been pulling your hair out and wasting your time. I don't even consider using FMP and SQL without a separate tool. I am hoping that FMP 17 or FMP 18 will be better in basic SQL capabilities beyond just showing an error message.

ERROR REPORTED FROM SQL TOOL:[08007] [FileMaker][FileMaker JDBC] FQL0021/(1:7): Parameter number 1 to the function "AVG" is not of the correct type.

You can't average a timestamp field: It doesn't make sense.

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

If I add a number field, called "number", then AVG works fine:

So, ....

-----

The answer to how you do what you want to do is to calculate, say, the amount of time in minutes, seconds, or whatever something took. So, for example, a possible good approach would be to have a starting TIMESTAMP and an ending TIMESTAMP. Then, you take the difference of these possibly in a calculated field. Then take the AVG of the calculated field using those numeric timestamp difference values.

Be careful, though, since (amazingly) FMP doesn't seem to understand its own TIMESTAMP data type with other FMP functions. You may need an intermediate CF for that conversion just to do the date math.

"timestamp" is really a number (of elapsed time), but if the user has chosen to return time/timestamp result in the calucaltion, that's perfectly legal and something I might want to display rather than the number of seconds. (for example):