AnsweredAssumed Answered

Problems with FMP GROUP BY in ExecuteSQL

Question asked by MorkAfur on Dec 22, 2012
Latest reply on Dec 26, 2012 by philmodjunk

Title

Problems with FMP GROUP BY in ExecuteSQL

Post

     When I try to run the query below (withouth the YEAR(CHECK_DATE), it works OK but doesn't properly summarize by year.

     For some reason, when I add the YEAR(CHECK_DATE) to the GROUP BY clause, FMP tells me I have a query error ("There is an error in the syntax of the query."). I had the same issue trying to select the current year in the "FROM" clause, but temporarily just hard coded the year to 2012.

     So, how do you have a summary listing like this:

     3 2010

     4 2011

     5 2012

     If you can't GROUP BY using a YEAR of a date field as I'm trying to do below?

     I don't have to have summary fields defined in the database to be able to use GROUP BY, right?

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

     SELECT SUM(NUM_THIS_MONTH) as Num_by_year , YEAR(CHECK_DATE) as YEAR_DATE
     FROM TABLE1
     WHERE Year(CHECK_DATE) = 2012
     Group by NUM_THIS_MONTH , YEAR(CHECK_DATE)
     order by CHECK_DATE DESC

     Thanks in advance,

     -m

Outcomes