{"id":4634,"date":"2013-03-13T10:25:43","date_gmt":"2013-03-13T18:25:43","guid":{"rendered":"http:\/\/seedcodenext.wordpress.com\/?p=938"},"modified":"2013-03-13T10:25:43","modified_gmt":"2013-03-13T18:25:43","slug":"sql-subqueries-in-filemaker","status":"publish","type":"post","link":"https:\/\/seedcode.com\/sql-subqueries-in-filemaker\/","title":{"rendered":"SQL Subqueries in FileMaker"},"content":{"rendered":"

I\u2019ve had some small pre-FileMaker experience with SQL and had it in my head that subqueries work in the FROM clause. \u00a0They do in MySQL etc, so I was frustrated when I couldn\u2019t get that to work in FileMaker\u2019s SQL as it\u2019s really needed in some cases.<\/p>\n

\"\"<\/a><\/p>\n

In a current project we have a case where there is a relationship from Contacts to Loans and we wanted to see the total Loan amount from the Contact context. \u00a0However, Contacts can be associated with a Loan using multiple roles, and we only want to have the Loan amount counted once for the Contact, otherwise the total will seem inflated. \u00a0To further complicate this, the Portal is filtered, and we want to see the filtering reflected in the total. \u00a0This, I thought was a job for SQL\u2026<\/p>\n

<\/p>\n

I first thought some kind of DISTINCT statement would allow me to just summarize the amount once per loan, but you can only apply DISTINCT to the field you want Summed. \u00a0For example,<\/p>\n

SELECT SUM ( DISTINCT \u201cLoan Amount\u201d )
\n<\/code><\/p><\/blockquote>\n

But this won\u2019t work, because the Loan Amount may not be unique across Loans!<\/p>\n

I can do this:<\/p>\n

SELECT DISTINCT \u201cLoanID\u201d, SUM ( \u201cLoanAmount\u201d )
\n<\/code><\/p><\/blockquote>\n

but that gives me the first Loan Number and Amount, not a total I can use. \u00a0If I add GROUP BY I get a little closer:<\/p>\n

SELECT DISTINCT \u201cLoanID\u201d, SUM ( \u201cLoanAmount\u201d ) FROM Loans GROUP BY \u201cLoanID\u201d
\n<\/code><\/p><\/blockquote>\n

but that gives me a list like this:<\/p>\n