10 Replies Latest reply on May 12, 2014 7:45 AM by beverly

    SQL ODBC Group By with function question

    lkeyes

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

        • 1. Re: SQL ODBC Group By with function question
          wimdecorte

          I'm not sure that MONTHNAME() is a supported SQL function.  Does the query generally work if you do not use that function?

          1 of 1 people found this helpful
          • 2. Re: SQL ODBC Group By with function question
            lkeyes

            The query works with MonthName() without the Group By,  and it works with the Group By without the MonthName()   I actually got the MonthName function from an ODBC reference, and it also works within ExecuteSQL .   

            • 3. Re: SQL ODBC Group By with function question
              beverly

              Yes, it's one of the valid functions with eSQL.

               

              I think the problem is that the function needs to make an "alias" of the column and then GROUP BY the alias:

               

              SELECT function(field) AS alias, SUM(anotherfield)

              FROM table

              GROUP BY alias

               

              Beverly

               

              p.s. OOPS! you said SQL ODBC and not ExecuteSQL() - the function - but my answer is the same, you need to try the alias

               

              Message was edited by: Beverly Voth

              • 4. Re: SQL ODBC Group By with function question
                lkeyes

                That's what I think is so weird.... I tried AS, (see #2 in my note above...)  

                 

                SELECT monthname(order_date) AS Zilch, sum(paid)

                FROM mom_export

                Group by Zilch

                 

                 

                From FM I get the ever-helpful question mark, and from my ODBC query I get:

                <eb1>FQL0007/(3:10): The column named "Zilch" does not exist in any table in the column reference's scope.

                State: HY000; Native: 8309; Origin: [FileMaker][FileMaker]</eb1>

                 

                Also, tried enclosing the alias within double quotes and single quotes. Also tried syntax without zilch. 

                 

                 

                 

                SELECT monthname(order_date), sum(paid)

                FROM mom_export

                Group BY monthname(order_date)

                 

                This returns the following error:

                <eb1>FQL0001/(3:11): There is an error in the syntax of the query.

                State: 42000; Native: 8310; Origin: [FileMaker][FileMaker]</eb1>

                 

                All this is with FMA v.13. 

                • 5. Re: SQL ODBC Group By with function question
                  beverly

                  hmmm. try to ORDER BY the "order_date" (not necessarily quoted)

                  Do you really want to sort on the month NAME? jan would be after feb...

                  Perhaps it's time to make a field in your db with the MonthName and/or the month as a number (thus sortable).

                   

                  Beverly

                  • 6. Re: SQL ODBC Group By with function question
                    lkeyes

                    hmmm. try to ORDER BY the "order_date" (not necessarily quoted)

                    Do you really want to sort on the month NAME? jan would be after feb...

                    Perhaps it's time to make a field in your db with the MonthName and/or the month as a number (thus sortable).

                    So, I schlepped the table into mySQL, and now this works:

                     

                    Select MONTH(Order_date), sum(paid)

                    FROM MOM_Export

                    GROUP BY MONTH(order_date);

                    As a point of interest, the same query using MONTHNAME() gives a hexedecimal value for the month (yikes!) and the correct total aggregated amounts.

                     

                    skitch1.png

                     

                    Yet if I query without the GROUP BY ... 

                     

                    Select MONTHNAME(Order_date), paid from MOM_Export;

                    I get the expected results.

                    skitch2.png

                    Curiouser and curiouser.

                    • 7. Re: SQL ODBC Group By with function question
                      user19752

                      It is simple, "The GROUP BY clause specifies the names of one or more fields by which the returned values should be grouped."

                      so you need to make "field in the table" to group, as Beverly said.

                      https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                       

                      Result on mySQL is interesting but not so helpful for FM SQL.

                      1 of 1 people found this helpful
                      • 8. Re: SQL ODBC Group By with function question
                        lkeyes

                        Well, it looks like in FileMaker that a GROUP BY has to be on an existing "real" field.....rather than a "derived" field.  So, Beverly's suggestion to add a calculated field to the table would probably be the optimum solution.

                         

                        It can be done in other SQLs, however, sometimes using nested SELECTS or other tricks that can't be done in FM SQL. 

                         

                        Result on mySQL is interesting but not so helpful for FM SQL

                        Noted.

                         

                        I had to try it though, because I thought I was going crazy.  

                         

                        I'd love to see fuller SQL support in FM, with better error messages. 

                         

                        More at: http://stackoverflow.com/questions/10399067/how-do-i-group-by-on-calculated-columns 

                         

                        Thanks everyone for your help!

                        • 9. Re: SQL ODBC Group By with function question
                          beverly

                          +1.

                           

                          Beverly

                          • 10. Re: SQL ODBC Group By with function question
                            beverly

                            I believe in my testing, this was ok for some aggregates, but not others. I tested a lot, so don't know for sure. YMMV or YKMV

                             

                            Mostly I believe that your failure, lkeyes, comes from using the function MONTHNAME(dtField) as one of the columns AND a SUM() function on the other.

                            in eSQL:

                                 GROUP BY does work on fields without additional functions applied to them

                                 GROUP BY does NOT work if the column # is used (it works in some other SQL dbs)

                                 GROUP BY does NOT appear to work using an alias (of even a real field)

                                 GROUP BY using multi-fields, of course, gives you unique results (as in other SQL dbs)

                             

                            Beverly

                            P.S. I always include a Year_Month field (auto-enter) for any date field that might need to be sorted (or grouped, in this case). It's text, but really handy in eSQL, in charts, in reports as it sorts alpha-numerically in any country! You can take the values and convert (or format) as 'MONTHNAME YEAR', should you desire.