# Grouping Examples

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

You can use aggregate functions without a `groupBy` argument or use them with a `groupBy` argument with optional types `CUBE` or `ROLLUP`.

## Get Average Amount on Opportunities by Campaign

Use the aggregate function with a `groupBy` argument to find the average amount of all your opportunities by campaign.

```sfdocs-code {"lang":"graphql", "title": "Query All Opportunities by Campaigns", "src": "" }
query OppsByCampaign {
  uiapi {
    aggregate {
      Opportunity (groupBy: { CampaignId: { group: true } }) {
        edges {
          node {
            aggregate {
              Amount {
                avg {
                  value
                }
              }
            }
          }
        }
      }
    }
  }
}
```

This query returns the average amount of all your opportunities by campaign.

```sfdocs-code {"lang":"json", "title": "Response for Opportunities by Campaign Query", "src": "" }
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Opportunity": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Amount": {
                    "avg": {
                      "value": 185806.4516129032
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

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

## Get Average Amount on Opportunities by Account ID and Name

You can also use the aggregate function with a `groupBy` argument to group by multiple fields.

```sfdocs-code {"lang":"graphql", "title": "Query All Opportunities by Account ID and Name", "src": "" }
query OppsByCampaign {
  uiapi {
    aggregate {
      Opportunity(groupBy: {AccountId: {group: true}, Name: { group: true }}) {
        edges {
          node {
            aggregate {
              Amount {
                avg {
                  value
                }
              }
            }
          }
        }
      }
    }
  }
}
```

This query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with GROUP BY clause and multiple fields", "src": "" }
SELECT AVG(Amount) Amount__a
FROM Opportunity
GROUP BY AccountId, Name
```

## Get the Number of Accounts for Each Industry and Group By Industry

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

This query provides the following response.

```sfdocs-code {"lang":"json", "title": "Response for query using groupBy only", "src": "" }
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null
                  },
                  "Name": {
                    "count": {
                      "value": 18
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Apparel"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Biotechnology"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Construction"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Consulting"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Education"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Electronics"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Energy"
                  },
                  "Name": {
                    "count": {
                      "value": 3
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Hospitality"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Transportation"
                  },
                  "Name": {
                    "count": {
                      "value": 1
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

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

## Get Accounts and Group By Year

```sfdocs-code {"lang":"graphql", "title": "Query using groupBy and Date Function", "src": "" }
query GroupAccountsByYear {
  uiapi {
    aggregate {
      Account (groupBy: { CreatedDate: { function : CALENDAR_YEAR } } ) {
        edges {
          node {
            aggregate {
              Name {
                count {
                  value
                }
              }
              CreatedDate {
                calendarYear {
                  value
                }
              }
            }
          }
        }
      }
    }
  }
}
```

This query provides the following response.

```sfdocs-code {"lang":"json", "title": "Response for groupBy and Date function query", "src": "" }
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "count": {
                      "value": 24
                    }
                  },
                  "CreatedDate": {
                    "calendarYear": {
                      "value": 2022
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Name": {
                    "count": {
                      "value": 5
                    }
                  },
                  "CreatedDate": {
                    "calendarYear": {
                      "value": 2023
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with GROUP BY clause", "src": "" }
SELECT Count(Name), CALENDAR_YEAR(CreatedDate)
FROM Account
GROUP BY CALENDAR_YEAR(CreatedDate)
```

## Get the Sum of Accounts for Combination of Type and Industry

```sfdocs-code {"lang":"graphql", "title": "Query using groupBy and CUBE type", "src": "" }
query aggregates {
  uiapi {
    aggregate {
      Account(groupBy: { type: CUBE, Industry: { group: true }, Type: { group: true } }) {
        edges {
          node {
            aggregate {
              Industry {
                value
                grouping {
                  value
                }
              }
              Type {
                value
                grouping {
                  value
                }
              }
              NumberOfEmployees {
                sum {
                  value
                }
              }
            }
          }
        }
      }
    }
  }
}
```

This query provides the following response.

```sfdocs-code {"lang":"json", "title": "Response for groupBy and CUBE type query", "src": "" }
{
  "data": {
    "uiapi": {
      "aggregate": {
        "Account": {
          "edges": [
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Manufacturing",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": "Prospect",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 680
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Manufacturing",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 680
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Media",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": "Prospect",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 14668
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Media",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 14668
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Technology",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": "Customer",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": "Technology",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 15348
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "Type": {
                    "value": null,
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "Type": {
                    "value": "Prospect",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": 15348
                    }
                  }
                }
              }
            },
            {
              "node": {
                "aggregate": {
                  "Industry": {
                    "value": null,
                    "grouping": {
                      "value": 1
                    }
                  },
                  "Type": {
                    "value": "Customer",
                    "grouping": {
                      "value": 0
                    }
                  },
                  "NumberOfEmployees": {
                    "sum": {
                      "value": null
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  },
  "errors": []
}
```

The previous query is similar to the following SOQL statement.

```sfdocs-code {"lang":"sql", "title": "SOQL statement with GROUP BY CUBE clause", "src": "" }
SELECT Industry, Type, SUM(NumberOfEmployees)
FROM Account
GROUP BY CUBE(Industry, Type)
```

## See Also

[SOQL and SOSL Reference: GROUP BY CUBE](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_cube.htm)