SQL aggregation in FileMaker calculations

Expert

Use ExecuteSQL GROUP BY and aggregate functions to compute cross-table totals, counts, and statistics without relationships or portals.

What you'll learn

How GROUP BY aggregation works in ExecuteSQL
How to use HAVING to filter group results
How to extract a scalar aggregation from a multi-row result
How to parse an ExecuteSQL multi-row result into a FileMaker value list

ExecuteSQL's GROUP BY clause enables cross-table aggregation in a single calculation field. Instead of creating a relationship for every aggregation scenario, one SQL query can group, count, sum, and filter data from any table in the file -- returning results as a formatted text string that calculations can further process.

1/4

GROUP BY for per-group totals

GROUP BY collapses rows with the same value in the group column into a single result row, combining the other columns with aggregate functions.

FileMaker Script

// Count invoices and total amount per client
ExecuteSQL (
  "SELECT "ClientId", COUNT(*), SUM("Amount")
   FROM "Invoices"
   WHERE "Status" = 'Outstanding'
   GROUP BY "ClientId"
   ORDER BY SUM("Amount") DESC" ;
  "," ;   // field separator -- separates columns
  "|"    // row separator -- separates rows
)
// Returns: "C001,3,4500.00|C002,1,800.00|..."

ExecuteSQL advanced patterns FilterValues patterns and list manipulation