AnsweredAssumed Answered

SQL ODBC Group By with function question

Question asked by lkeyes on May 9, 2014
Latest reply on May 12, 2014 by beverly

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)

FROM mom_export

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)

FROM mom_export

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? :-)