salesforce_aggregate_query
Execute SOQL queries with GROUP BY and aggregate functions to summarize and analyze Salesforce data. Use for grouping records, applying statistical functions, and filtering results with WHERE and HAVING clauses.
Instructions
Execute SOQL queries with GROUP BY, aggregate functions, and statistical analysis. Use this tool for queries that summarize and group data rather than returning individual records.
NOTE: For regular queries without GROUP BY or aggregates, use salesforce_query_records instead.
This tool handles:
- GROUP BY queries (single/multiple fields, related objects, date functions)
- Aggregate functions: COUNT(), COUNT_DISTINCT(), SUM(), AVG(), MIN(), MAX()
- HAVING clauses for filtering grouped results
- Date/time grouping: CALENDAR_YEAR(), CALENDAR_MONTH(), CALENDAR_QUARTER(), FISCAL_YEAR(), FISCAL_QUARTER()
Examples:
- Count opportunities by stage:
- objectName: "Opportunity"
- selectFields: ["StageName", "COUNT(Id) OpportunityCount"]
- groupByFields: ["StageName"]
- Analyze cases by priority and status:
- objectName: "Case"
- selectFields: ["Priority", "Status", "COUNT(Id) CaseCount", "AVG(Days_Open__c) AvgDaysOpen"]
- groupByFields: ["Priority", "Status"]
- Count contacts by account industry:
- objectName: "Contact"
- selectFields: ["Account.Industry", "COUNT(Id) ContactCount"]
- groupByFields: ["Account.Industry"]
- Quarterly opportunity analysis:
- objectName: "Opportunity"
- selectFields: ["CALENDAR_YEAR(CloseDate) Year", "CALENDAR_QUARTER(CloseDate) Quarter", "SUM(Amount) Revenue"]
- groupByFields: ["CALENDAR_YEAR(CloseDate)", "CALENDAR_QUARTER(CloseDate)"]
- Find accounts with more than 10 opportunities:
- objectName: "Opportunity"
- selectFields: ["Account.Name", "COUNT(Id) OpportunityCount"]
- groupByFields: ["Account.Name"]
- havingClause: "COUNT(Id) > 10"
Important Rules:
- All non-aggregate fields in selectFields MUST be included in groupByFields
- Use whereClause to filter rows BEFORE grouping
- Use havingClause to filter AFTER grouping (for aggregate conditions)
- ORDER BY can only use fields from groupByFields or aggregate functions
- OFFSET is not supported with GROUP BY in Salesforce
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| groupByFields | Yes | Fields to group by - must include all non-aggregate fields from selectFields | |
| havingClause | No | HAVING clause to filter results AFTER grouping (use for aggregate conditions) | |
| limit | No | Maximum number of grouped results to return | |
| objectName | Yes | API name of the object to query | |
| orderBy | No | ORDER BY clause - can only use grouped fields or aggregate functions | |
| selectFields | Yes | Fields to select - mix of group fields and aggregates. Format: 'FieldName' or 'COUNT(Id) AliasName' | |
| whereClause | No | WHERE clause to filter rows BEFORE grouping (cannot contain aggregate functions) |