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.
1query AvgOpportunityExample {
2 uiapi {
3 aggregate {
4 Opportunity {
5 edges {
6 node {
7 aggregate {
8 Amount {
9 avg {
10 value
11 displayValue
12 }
13 }
14 }
15 }
16 }
17 totalCount
18 }
19 }
20 }
21}The previous query is similar to the following SOQL statement.
1SELECT AVG(Amount)
2FROM OpportunityGet 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.
1query AvgAmountPerCampaign {
2 uiapi {
3 aggregate {
4 Opportunity (groupBy: { CampaignId: { group: true } } ) {
5 edges {
6 node {
7 aggregate {
8 CampaignId { value }
9 Amount { avg { value } }
10 }
11 }
12 }
13 }
14 }
15 }
16}The previous query is similar to the following SOQL statement.
1SELECT CampaignId, AVG(Amount)
2FROM Opportunity
3GROUP BY CampaignIdGet Total Count of Records
Return the total number of account records.
1query AccountsCount {
2 uiapi {
3 aggregate {
4 Account {
5 edges {
6 node {
7 aggregate {
8 Id { count { value } }
9 }
10 }
11 }
12 }
13 }
14 }
15}Example Response for Total Count of Records
The previous query returns this response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Id": {
11 "count": {
12 "value": 29
13 }
14 }
15 }
16 }
17 }
18 ]
19 }
20 }
21 }
22 },
23 "errors": []
24}The previous query is similar to the following SOQL statement.
1SELECT COUNT(Id)
2FROM AccountGet Total Count of Records with Grouping
To use the count function and group by a field, use Id { count { value } } on the aggregate field.
1query AccountsCountGroupBy {
2 uiapi {
3 aggregate {
4 Account ( groupBy: { Industry: { group: true } } ) {
5 edges {
6 node {
7 aggregate {
8 Industry { value }
9 Id { count { value } }
10 }
11 }
12 }
13 }
14 }
15 }
16}Example Response for Total Count of Records with Grouping
The previous query returns this response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "value": null
12 },
13 "Id": {
14 "count": {
15 "value": 18 //number of accounts without an Industry specified
16 }
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "Industry": {
25 "value": "Apparel"
26 },
27 "Id": {
28 "count": {
29 "value": 1
30 }
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "Industry": {
39 "value": "Biotechnology"
40 },
41 "Id": {
42 "count": {
43 "value": 1
44 }
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "Industry": {
53 "value": "Construction"
54 },
55 "Id": {
56 "count": {
57 "value": 1
58 }
59 }
60 }
61 }
62 },
63 {
64 "node": {
65 "aggregate": {
66 "Industry": {
67 "value": "Consulting"
68 },
69 "Id": {
70 "count": {
71 "value": 1
72 }
73 }
74 }
75 }
76 },
77 {
78 "node": {
79 "aggregate": {
80 "Industry": {
81 "value": "Education"
82 },
83 "Id": {
84 "count": {
85 "value": 1
86 }
87 }
88 }
89 }
90 },
91 {
92 "node": {
93 "aggregate": {
94 "Industry": {
95 "value": "Electronics"
96 },
97 "Id": {
98 "count": {
99 "value": 1
100 }
101 }
102 }
103 }
104 },
105 {
106 "node": {
107 "aggregate": {
108 "Industry": {
109 "value": "Energy"
110 },
111 "Id": {
112 "count": {
113 "value": 3
114 }
115 }
116 }
117 }
118 },
119 {
120 "node": {
121 "aggregate": {
122 "Industry": {
123 "value": "Hospitality"
124 },
125 "Id": {
126 "count": {
127 "value": 1
128 }
129 }
130 }
131 }
132 },
133 {
134 "node": {
135 "aggregate": {
136 "Industry": {
137 "value": "Transportation"
138 },
139 "Id": {
140 "count": {
141 "value": 1
142 }
143 }
144 }
145 }
146 }
147 ]
148 }
149 }
150 }
151 },
152 "errors": []
153}The previous query is similar to the following SOQL statement.
1SELECT Industry, COUNT(Id)
2FROM Account
3GROUP BY IndustryGet 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.
1query accounts {
2 uiapi {
3 query {
4 Account {
5 edges {
6 node {
7 Name {
8 value
9 }
10 }
11 }
12 }
13 }
14 aggregate {
15 Account {
16 edges {
17 node {
18 aggregate {
19 AnnualRevenue {
20 avg {
21 displayValue
22 }
23 }
24 }
25 }
26 }
27 }
28 }
29 }
30}Get Average Annual Revenue of Accounts
Return the average annual revenue of account records.
1query AvgRevenueAccountsExample {
2 uiapi {
3 aggregate {
4 Account {
5 edges {
6 node {
7 aggregate {
8 AnnualRevenue {
9 avg {
10 value
11 displayValue
12 }
13 }
14 }
15 }
16 }
17 totalCount
18 }
19 }
20 }
21}Example Response for Average Annual Revenue
The previous query returns this response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "AnnualRevenue": {
11 "avg": {
12 "value": 1071125000,
13 "displayValue": "$1,071,125,000"
14 }
15 }
16 }
17 }
18 }
19 ],
20 "totalCount": 24
21 }
22 }
23 }
24 },
25 "errors": []
26}The previous query is similar to the following SOQL statement.
1SELECT AVG(AnnualRevenue), FORMAT(AVG(AnnualRevenue))
2From AccountGet 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.
1query accounts {
2 uiapi {
3 aggregate {
4 Account {
5 edges {
6 node {
7 aggregate {
8 Industry {
9 countDistinct{
10 value
11 }
12 }
13 }
14 }
15 }
16 totalCount
17 }
18 }
19 }
20}Example Response for Distinct Count
The previous query returns this response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "countDistinct": {
12 "value": 9
13 }
14 }
15 }
16 }
17 }
18 ],
19 "totalCount": 24
20 }
21 }
22 }
23 },
24 "errors": []
25}The previous query is similar to the following SOQL statement.
1SELECT COUNT_DISTINCT(Industry)
2From AccountGet Maximum Value of a Field
To return the maximum value of a field, use the max function.
1query MaxFieldGroupBy {
2 uiapi {
3 aggregate {
4 Campaign (groupBy: { Name: { group: true } } ) {
5 edges {
6 node {
7 aggregate {
8 Name { value }
9 BudgetedCost { max { value } }
10 }
11 }
12 }
13 }
14 }
15 }
16}Example Response for Maximum Value of a Field
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Campaign": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Name": {
11 "value": "DM VIP Campaign"
12 },
13 "BudgetedCost": {
14 "max": {
15 "value": 25000
16 }
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "Name": {
25 "value": "GC Product Webinar"
26 },
27 "BudgetedCost": {
28 "max": {
29 "value": 10000
30 }
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "Name": {
39 "value": "International Engineers Trade Show"
40 },
41 "BudgetedCost": {
42 "max": {
43 "value": 50000
44 }
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "Name": {
53 "value": "User Conference"
54 },
55 "BudgetedCost": {
56 "max": {
57 "value": 100000
58 }
59 }
60 }
61 }
62 }
63 ]
64 }
65 }
66 }
67 },
68 "errors": []
69}The previous query is similar to the following SOQL statement.
1SELECT Name, MAX(BudgetedCost)
2FROM Campaign
3GROUP BY NameGet 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.
1query SumAmountGroupBy {
2 uiapi {
3 aggregate {
4 Opportunity ( where: {
5 and: [{ IsClosed: { eq: false }},
6 { Probability: { gt: 60 }
7 }]
8 },
9 groupBy: { Name: { group: true } } ) {
10 edges {
11 node {
12 aggregate {
13 Name { value }
14 Amount { sum { value } }
15 }
16 }
17 }
18 }
19 }
20 }
21}Example Response of Open Opportunities Sum
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Opportunity": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Name": {
11 "value": "Express Logistics SLA"
12 },
13 "Amount": {
14 "sum": {
15 "value": 120000
16 }
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "Name": {
25 "value": "United Oil Installations"
26 },
27 "Amount": {
28 "sum": {
29 "value": 270000
30 }
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "Name": {
39 "value": "United Oil Office Portable Generators"
40 },
41 "Amount": {
42 "sum": {
43 "value": 125000
44 }
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "Name": {
53 "value": "United Oil Refinery Generators"
54 },
55 "Amount": {
56 "sum": {
57 "value": 270000
58 }
59 }
60 }
61 }
62 },
63 {
64 "node": {
65 "aggregate": {
66 "Name": {
67 "value": "University of AZ Installations"
68 },
69 "Amount": {
70 "sum": {
71 "value": 100000
72 }
73 }
74 }
75 }
76 }
77 ],
78 "totalCount": 5
79 }
80 }
81 }
82 },
83 "errors": []
84}The previous query is similar to the following SOQL statement.
1SELECT SUM(Amount), Name
2FROM Opportunity
3WHERE IsClosed = false AND Probability > 60
4GROUP BY NameSee Also
SOQL and SOSL Reference: Aggregate Functions
j