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

Outcomes