SQL GROUP BY ISSUE
I'm confused why FMP doesn't like the following SQL:
SELECT YEAR(date_first_received), count(year(date_first_received)) as cnt from vendors group by year(date_first_received)order by date_first_received DESC
In particular, it doesn't seem to like my GROUP BY expression.
The date_first_received field is a standard date field with no strict data formatting (per FMP's SQL reference).
This same type of query works fine in, say, MySQL or Oracle, so I'm wondering if there is some GROUP BY restriction in FMP. I can't seem to find many FMP-specific examples.
I'm testing these queries in a separate database console so I can use JDBC and also since I don't just get FMP's maddening "?" result with zero helpful information. :(
(Just to pile on for a second, since I've already requested an increase in SQL functionality, IMHO, FMP's current "Data Viewer" window is so tiny, windows and fonts can't be resized, non-helpful "?" error messages and the like, it's really not useful for SQL work against the database (for other than the simplest queries)-- a separate database console is the only way to go that I've found.)
Thanks in advance.