Am accessing a Filemaker table via ODBC, and am getting a syntax error which I can't quite figure out... I want to get a total of sales by month.
SELECT monthname(order_date), sum(paid)
GROUP BY monthname(order_date)
This throws a syntax error.
I've tried the following:
1. if I GROUP BY order_date (without the monthname function) it works, but I get the totals for the daily orders of course.
2. if I attempt to rename the column using AS, that gives an error "The column named "<columname>" does not exist in any table in the column reference's scope".
Select monthname(order_date) AS Mymonth, sum(paid)
GROUP BY Mymonth
3. Tried creating a temporary table/cursor, but that doesn't appear to be supported when using ODBC (as it isn't when using ExecuteSQL)
Any hope? :-)