2 Replies Latest reply on Apr 30, 2013 6:26 AM by mikebeargie

    SUM of records in two tables using ExecuteSQL()

    DavidJondreau

      I'm trying to get the sum of records in two different tables using ExecuteSQL().

       

      I have a date field in two tables (one representing what is paid to workers, the other what is charged a client), along with a total field. I'd like to end up with a list of dates and the totals from each table for each date. I think this is SUM() and a JOIN, but I can't get the totals to come out right...Maybe it's not a join but multiple selects?

       

      Any help would be appreciated.

       

      Thanks,

      DJ

        • 1. Re: SUM of records in two tables using ExecuteSQL()
          taylorsharpe

          Does one of the tables have a date field that includes all possible dates or are you having to add in dates that may be omitted?  I still see you having to loop through to add things up, but maybe it can just be done with SQL.  Do you have an example database and ExecuteSQL that we could look at and play with?

          • 2. Re: SUM of records in two tables using ExecuteSQL()
            mikebeargie

            This is a JOIN, but the trick is what kind. An inner join (or plain join) will join where there are values in both tables, but not when a value from one is missing in the other. A left join (outer join) returns all the results in the left table, but only matching results in the right table (right join is the opposite of this). A Union will treat two tables as if you're dealing with one, but that doesn't work for you since you want two separate amount columns.

             

            Now, I'm not sure filemaker respects the FULL JOIN syntax, but in MySQL (what I'm familiar with), a FULL JOIN returns all rows from either side of the join, regardless of matches between tables (empty cells are returned as NULL, but probably as ? in filemaker). It sounds like you need this (unless you only want rows where dates match in both tables, which you'd just use JOIN or INNER JOIN for instead).

             

            Now onto the query, where we will only return the left table's date as a reference:

             

            ExecuteSQL("

            SELECT a.date, SUM(a.amount), SUM(b.amount)

               FROM table1 a

               FULL JOIN table2 b ON a.date = b.date

            WHERE a.date > ? AND b.date > ?

            GROUP BY a.date

            " ; "" ; "" ; findDate ; findDate )

             

            Of course modify the WHERE clause to what you need it to be. I believe you could possibly nest some joins inside of a UNION statement, as the above will return a blank date value wherever there is a date in the right table that does not exist in the left table, but I think the above should get you thinking on the right track. I was quoting this off the top of my head, not sure if it really works.

             

            I would highly recommend Beverly Voth's article about ExecuteSQL as required reading:

            http://www.filemakerhacks.com/?p=6406

             

            Also W3 school SQL section:

            http://www.w3schools.com/sql/