# Aggregate Examples

| AVAILABLE API VERSION |
| --------------------- |
| API v58.0 and later   |

Use aggregate functions to analyze your query results. Aggregate functions include `avg`, `count`, `countDistinct`, `min`, `max`, and `sum`.

## Get Average Opportunity Amount

Return the average amount on all opportunities.

You can use aggregate functions without a `groupBy` argument, such as using `totalCount` to find the total number of query results or using the `avg` function to find the average amount for all opportunities.

```sfdocs-code {"lang":"graphql", "title": "Query Average Opportunity Amount", "src": "" }
query AvgOpportunityExample {
  uiapi {
    aggregate {
      Opportunity {
        edges {
          node {
            aggregate {
              Amount {
                avg {
                  value
                  displayValue
                }
              }
            }
          }
        }
        totalCount
      }
    }
  }
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with AVG() function", "src": "" }
SELECT AVG(Amount)
FROM Opportunity
```

## Get Average Amount of Opportunities By Campaign

Using the `groupBy` argument with aggregation functions enables you to perform further analysis on your results, like finding the average amount of all opportunities by campaign.

```sfdocs-code {"lang":"graphql", "title": "Query Opportunities and Average Amount per Campaign", "src": "" }
query AvgAmountPerCampaign {
  uiapi {
    aggregate {
      Opportunity (groupBy: { CampaignId: { group: true } } ) {
        edges {
          node {
            aggregate {
              CampaignId { value }
              Amount { avg { value } }
            }
          }
        }
      }
    }
  }
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with AVG() and GROUP BY", "src": "" }
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
```

## Get Total Count of Records

Return the total number of account records.

```sfdocs-code {"lang":"graphql", "title": "Query total count of account records", "src": "" }
query AccountsCount {
  uiapi {
    aggregate {
      Account {
        edges {
          node {
            aggregate {
              Id { count { value } }
            }
          }
        }
      }
    }
  }
}
```

### Example Response for Total Count of Records

The previous query returns this response.

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Id": {
                    "count": {
                      "value": 29
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with COUNT", "src": "" }
SELECT COUNT(Id)
FROM Account
```

## Get Total Count of Records with Grouping

To use the `count` function and group by a field, use `Id { count { value } }` on the `aggregate` field.

```sfdocs-code {"lang":"graphql", "title": "Query", "src": "" }
query AccountsCountGroupBy {
  uiapi {
    aggregate {
      Account ( groupBy: { Industry: { group: true } } ) {
        edges {
          node {
            aggregate {
              Industry { value }
              Id { count { value } }
            }
          }
        }
      }
    }
  }
}
```

### Example Response for Total Count of Records with Grouping

The previous query returns this response.

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null
                  },
                  "Id": {
                    "count": {
                      "value": 18 //number of accounts without an Industry specified
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Apparel"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Biotechnology"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Construction"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Consulting"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Education"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Electronics"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Energy"
                  },
                  "Id": {
                    "count": {
                      "value": 3
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Hospitality"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Transportation"
                  },
                  "Id": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with COUNT and GROUP BY", "src": "" }
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry
```

## Get Accounts and Average Revenue of All Accounts

Return all accounts and their average annual revenue.

You can't simultaneously query both the `record` and `aggregate` fields within `RecordAggregate`. Query the records and aggregation separately like this.

```sfdocs-code {"lang":"graphql", "title": "Query accounts and average revenue", "src": "" }
query accounts {
  uiapi {
    query {
      Account {
        edges {
          node {
            Name {
              value
            }
          }
        }
      }
    }
    aggregate {
      Account {
        edges {
          node {
            aggregate {
              AnnualRevenue {
                avg {
                  displayValue
                }
              }
            }
          }
        }
      }
    }
  }
}
```

## Get Average Annual Revenue of Accounts

Return the average annual revenue of account records.

```sfdocs-code {"lang":"graphql", "title": "Query Average Annual Revenue of Accounts", "src": "" }
query AvgRevenueAccountsExample {
  uiapi {
    aggregate {
      Account {
        edges {
          node {
            aggregate {
              AnnualRevenue {
                avg {
                  value
                  displayValue
                }
              }
            }
          }
        }
        totalCount
      }
    }
  }
}
```

### Example Response for Average Annual Revenue

The previous query returns this response.

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "AnnualRevenue": {
                    "avg": {
                      "value": 1071125000,
                      "displayValue": "$1,071,125,000"
                    }
                  }
                }
              }
            }
          ],
          "totalCount": 24
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with AVG and FORMAT", "src": "" }
SELECT AVG(AnnualRevenue), FORMAT(AVG(AnnualRevenue))
From Account
```

## Get Distinct Number of Industries

Return the number of industries.  To return the number of distinct non-null field values matching the query criteria, use the `countDistinct` function.

```sfdocs-code {"lang":"graphql", "title": "Query Disctint Count of Industries", "src": "" }
query accounts {
    uiapi {
      aggregate {
          Account {
              edges {
                  node {
                      aggregate {
                        Industry {
                          countDistinct{
                            value
                          }
                        }
                      }
                  }
              }
            totalCount
          }
        }
    }
}
```

### Example Response for Distinct Count

The previous query returns this response.

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "countDistinct": {
                      "value": 9
                    }
                  }
                }
              }
            }
          ],
          "totalCount": 24
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with COUNT_DISTINCT", "src": "" }
SELECT COUNT_DISTINCT(Industry)
From Account
```

## Get Maximum Value of a Field

To return the maximum value of a field, use the `max` function.

```sfdocs-code {"lang":"graphql", "title": "Query Campaigns with Maximum BudgetedCost Field", "src": "" }
query MaxFieldGroupBy {
  uiapi {
    aggregate {
      Campaign (groupBy: { Name: { group: true } } ) {
        edges {
          node {
            aggregate {
              Name { value }
              BudgetedCost { max { value } }
            }
          }
        }
      }
    }
  }
}
```

### Example Response for Maximum Value of a Field

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Campaign": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "DM VIP Campaign"
                  },
                  "BudgetedCost": {
                    "max": {
                      "value": 25000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "GC Product Webinar"
                  },
                  "BudgetedCost": {
                    "max": {
                      "value": 10000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "International Engineers Trade Show"
                  },
                  "BudgetedCost": {
                    "max": {
                      "value": 50000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "User Conference"
                  },
                  "BudgetedCost": {
                    "max": {
                      "value": 100000
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with MAX() and GROUP BY", "src": "" }
SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name
```

## Get Sum of Open Opportunities with More Than 60% Probability

To return the sum of a numeric field, use the `sum` function. This example returns the opportunity amount sum grouped by name.

```sfdocs-code {"lang":"graphql", "title": "Query Sum of Open Opportunities", "src": "" }
query SumAmountGroupBy {
  uiapi {
    aggregate {
      Opportunity ( where: {
        and: [{ IsClosed: { eq: false }},
              { Probability: { gt: 60 }
             }]
           },
           groupBy: { Name: { group: true } } ) {
        edges {
          node {
            aggregate {
              Name { value }
              Amount { sum { value } }
            }
          }
        }
      }
    }
  }
}
```

### Example Response of Open Opportunities Sum

```json
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Opportunity": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "Express Logistics SLA"
                  },
                  "Amount": {
                    "sum": {
                      "value": 120000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "United Oil Installations"
                  },
                  "Amount": {
                    "sum": {
                      "value": 270000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "United Oil Office Portable Generators"
                  },
                  "Amount": {
                    "sum": {
                      "value": 125000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "United Oil Refinery Generators"
                  },
                  "Amount": {
                    "sum": {
                      "value": 270000
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "value": "University of AZ Installations"
                  },
                  "Amount": {
                    "sum": {
                      "value": 100000
                    }
                  }
                }
              }
            }
          ],
          "totalCount": 5
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with SUM() and GROUP BY", "src": "" }
SELECT SUM(Amount), Name
FROM Opportunity
WHERE IsClosed = false AND Probability > 60
GROUP BY Name
```

## See Also

[Grouping Examples](/docs/platform/graphql/guide/aggregate-groupby-examples.md)

[SOQL and SOSL Reference: Aggregate Functions](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm)