7 Replies Latest reply on Apr 17, 2014 12:22 PM by philmodjunk

    sum function

    GautamBagaria

      Title

      sum function

      Post

           can someone please tell me how to get the sum of all records from table1 field 1 into table 2 field2?

        • 1. Re: sum function
          philmodjunk

               If your relationship between table 1 and table 2 is:

               Table 1::any field X Table 2::any field ---> this is using X, the cartesian join operator instead of the default = operator.

               then a calculation field defined in Table 2 like this:

               Sum ( table 1::NumberField )

               will compute the sum of NumberField across all the records in Table 1

          • 2. Re: sum function
            GautamBagaria

                 thank you very much, i overlooked that fact that my relationship was not a cart join.

            • 3. Re: sum function
              philmodjunk

                   ExceuteSQL in FileMaker 12 or newer can also compute this total without needing a cartesian join relationship:

                   ExecuteSQL ( "Select ( Sum ( NumberField ) FROM \"Table 1\" "; "" ; ""  )

              • 4. Re: sum function
                GautamBagaria

                     can i add a condition to this?

                     like sum ( table1::field1) where table1.field2 = table2.field3 or something like that?

                • 5. Re: sum function
                  philmodjunk

                       Yes, but watch the syntax. In SQL, it's Sum ( Table1.field1 ) not sum ( Table1::field1 ) like you do in a FileMaker calculation.

                  • 6. Re: sum function
                    GautamBagaria

                         or rather i should ask...

                         how do i use this properly....

                         ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

                         for

                         executesql ("select Sum(L.Original) from Ledger L, summary S where L.category = 'S.category'....)

                         here my 'S.category' is actually a variable which would be the current value set in the summary table's category field.

                          

                    • 7. Re: sum function
                      philmodjunk

                           That's not quite enough info. From what context, summary or Ledger will this expression evaluate.

                           

                                my 'S.category' is actually a variable which would be the current value set in the summary table's category field.

                           A variable or a field? enclosing S.category in single quotes makes it literal text. It won't refer to either a field or a variable. If it's a field, is this a global field? If not, in which record of category would this value be entered? If it's a variable, you might be able to use this syntax:

                           executesql ("select Sum(L.Original) from Ledger L, summary S where L.category = ? " ; "" ; "" ; $$VariableNameGoesHere )

                           You may be able to put Summary::Category in place of the variable, but only if the field is global or, if not global if the context for the calculation is specified as Summary or Ledger with a relationship that matches to the correct record in Summary.

                           FileMaker SQL Resources: PDF that can be reached from the help menu via Product Documentation | More Documentation: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                           Free SQL Explorer from SeedCode: http://www.seedcode.com/cp-app/ste_cat/sqlxfree