4 Replies Latest reply on Jul 16, 2014 5:00 PM by philmodjunk

    SQL Count(*) and Filemaker Fields

    tigerdwn

      Title

      SQL Count(*) and Filemaker Fields

      Post

           I am trying to run the following query but am receiving the "?"

           If I remove the Count(*) line, the the query works.  Same thing if I remove the two lines after the count(*).  What am I missing?

           ExecuteSQL (
           "SELECT  
                   count(*) as TouchCount,
                   
                   Year(MeetingDate) as TouchYear,
                   
                   Month(MeetingDate) as TouchMonth       
               
                 FROM AccountTouches AS s
                 WHERE \"_fkAccountID\" = ?  
                 GROUP BY YEAR(MeetingDate), Month(MeetingDate)
                 ORDER BY YEAR(MeetingDate), Month(MeetingDate)     
            
            
            "
           ; " | " ; Char(13) ;100 )
            
            
           Here is the solution.  PhilModJunk made me think differently about the problem.  The solution is to group by the column name and not applying a function to the column.  Thank you for your help.
            
                ExecuteSQL (
                "SELECT 
                     Month(MeetingDate) as TouchMonth,
                     Year(MeetingDate) as TouchYear,
                     count(*) as TouchCount
                            
                      FROM AccountTouches AS s
                      WHERE \"_fkAccountID\" = ?
                      Group By MeetingDate
                 
                     
                        
                       
                         "
                ; "  " ; Char(13) ;370 )

            

        • 1. Re: SQL Count(*) and Filemaker Fields
          philmodjunk

               Count() is an aggregate function applied to either the entire record set or a group produced by the GROUP BY clause. Year and Month are not aggregate functions and I think that leads to an error here in your syntax.

               Since your query does not actually refer to TouchYear and TouchMonth anywhere but in the "AS" clauses, why do you need them?

          • 2. Re: SQL Count(*) and Filemaker Fields
            tigerdwn

                 MSSQL and MYSQL respond to this Select statement as long as you have the ORDER BY and GROUP BY clauses defined.   Is Filemaker looking for a different syntax?  

            • 3. Re: SQL Count(*) and Filemaker Fields
              philmodjunk

                   I'm not sure. I'm far from expert with SQL myself, just looking for a possible reason for the ? syntax error result.

                   I don't think that you need both the ORDER BY and GROUP BY clauses here though that doesn't seem to be the cause of this issue here.

                   I just looked up "Group BY" in FileMaker Inc.'s SQL reference doc and found this example:

                   SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id

                   The only obvious difference that I see from your SQL is that the aggregate function is listed last.

                   Also from the same document:

                   "columns must match the column expression used in the SELECT clause. A column expression can be one or more field names of the database table separated by commas."

              • 4. Re: SQL Count(*) and Filemaker Fields
                philmodjunk

                     Hmmm, just had a light bulb pop on. Maybe the GroupBy and OrderBY clauses need to refer to these values by TouchYear and TouchMonth.