GraphQL Aggregations

introduction aggregations let you compute rollups — counts, sums, averages, minimums, maximums, and grouped breakdowns — directly in graphql, so you can answer analytical questions in a single round trip instead of exporting raw records and calculating externally every object collection has an aggregate companion field named aggregate \<object> (for example, aggregate product , aggregate invoice , aggregate subscription ) the aggregate field accepts the same where and page arguments as the regular collection, plus a groupby argument this enables one round trip questions such as number of active products by category total pipeline value by stage and currency invoice totals by status supported functions within an aggregate \<object> selection you can request any of the following count(field \<field>, distinct \<bool>) sum(field \<field>) min(field \<field>) max(field \<field>) avg(field \<field>) the example below counts active products grouped by category query { aggregate product( where { status { eq "active" } } groupby \[productcategory] ) { productcategory count(field id, distinct true) } } { "data" { "result" { "aggregate product" \[ { "productcategory" "software", "count" 5 }, { "productcategory" "hardware", "count" 3 }, { "productcategory" "services", "count" 2 } ] } } } you can combine multiple functions in one query the following returns the count and total amount of invoices by status query { aggregate invoice(groupby \[status]) { status count(field id, distinct true) sum(field amount) } } when no groupby is supplied, the aggregate returns a single summary row query { aggregate subscription { count(field id, distinct true) sum(field quantity) min(field quantity) max(field quantity) avg(field quantity) } } aliased functions use graphql field aliases to return more than one variant of the same function in a single query — for example, distinct counts of two different fields query { aggregate subscription { count id count(field id, distinct true) count customerid count(field customerid, distinct true) } } { "data" { "result" { "aggregate subscription" \[ { "count id" 150, "count customerid" 75 } ] } } } grouping with groupby the groupby argument breaks results into one row per distinct combination of the grouped fields it accepts either a single field or an array of fields — both forms are equivalent \# array form aggregate product(groupby \[productcategory]) { } \# scalar form (equivalent) aggregate product(groupby productcategory) { } you can group by up to 3 fields query { aggregate product( where { status { eq "active" } } groupby \[productcategory, pricemodel, billingperiod] ) { productcategory pricemodel billingperiod count(field id, distinct true) } } a groupby with no aggregate functions behaves like select distinct — it returns the distinct combinations of the grouped fields query { aggregate product(groupby \[status]) { status } } grouped results support pagination through the page argument request successive pages by advancing the cursor query { aggregate product( where { status { eq "active" } } groupby \[productcategory] page { cursor 0, limit 2 } ) { productcategory count(field id, distinct true) } } multi currency aggregations currency amounts are never auto converted across currencies to aggregate monetary fields in a multi currency org, group by currencyisocode so each currency is returned on its own row query { aggregate invoice(groupby \[currencyisocode]) { currencyisocode count(field id, distinct true) sum(field amount) } } { "data" { "result" { "aggregate invoice" \[ { "currencyisocode" "usd", "count" 50, "sum" 10000 00 }, { "currencyisocode" "eur", "count" 30, "sum" 8000 00 } ] } } } validation rules aggregate queries are validated before they run the following are rejected with an error query why it fails sum(field name) sum , avg , min , and max require a numeric field — they cannot be applied to text fields count with no field argument count requires a field argument (for example count(field id, distinct true) ) groupby with more than 3 fields groupby is capped at 3 fields page { limit 1001 } the aggregate page limit cannot exceed 1000 related guides query nue objects using graphql docid 9j6pcigzvpcgzsdikcwc — endpoints, supported objects, filtering, pagination, and introspection graphql relationship traversal docid\ vemqp0s7ggc4wyrgylgcp — query nested and reverse relationships in a single round trip