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.
1query OppsByCampaign {
2 uiapi {
3 aggregate {
4 Opportunity (groupBy: { CampaignId: { group: true } }) {
5 edges {
6 node {
7 aggregate {
8 Amount {
9 avg {
10 value
11 }
12 }
13 }
14 }
15 }
16 }
17 }
18 }
19}This query returns the average amount of all your opportunities by campaign.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Opportunity": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Amount": {
11 "avg": {
12 "value": 185806.4516129032
13 }
14 }
15 }
16 }
17 }
18 ]
19 }
20 }
21 }
22 },
23 "errors": []
24}The previous query is similar to the following SOQL statement.
1SELECT CampaignId, AVG(Amount)
2FROM Opportunity
3GROUP BY CampaignIdGet 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.
1query OppsByCampaign {
2 uiapi {
3 aggregate {
4 Opportunity(groupBy: {AccountId: {group: true}, Name: { group: true }}) {
5 edges {
6 node {
7 aggregate {
8 Amount {
9 avg {
10 value
11 }
12 }
13 }
14 }
15 }
16 }
17 }
18 }
19}This query is similar to the following SOQL statement.
1SELECT AVG(Amount) Amount__a
2FROM Opportunity
3GROUP BY AccountId, NameGet the Number of Accounts for Each Industry and Group By Industry
1query industry {
2 uiapi {
3 aggregate {
4 Account ( groupBy: { Industry: { group: true } } ) {
5 edges {
6 node {
7 aggregate {
8 Industry {
9 value
10 }
11 Name {
12 count {
13 value
14 }
15 }
16 }
17 }
18 }
19 }
20 }
21 }
22}This query provides the following response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "value": null
12 },
13 "Name": {
14 "count": {
15 "value": 18
16 }
17 }
18 }
19 }
20 },
21 {
22 "node": {
23 "aggregate": {
24 "Industry": {
25 "value": "Apparel"
26 },
27 "Name": {
28 "count": {
29 "value": 1
30 }
31 }
32 }
33 }
34 },
35 {
36 "node": {
37 "aggregate": {
38 "Industry": {
39 "value": "Biotechnology"
40 },
41 "Name": {
42 "count": {
43 "value": 1
44 }
45 }
46 }
47 }
48 },
49 {
50 "node": {
51 "aggregate": {
52 "Industry": {
53 "value": "Construction"
54 },
55 "Name": {
56 "count": {
57 "value": 1
58 }
59 }
60 }
61 }
62 },
63 {
64 "node": {
65 "aggregate": {
66 "Industry": {
67 "value": "Consulting"
68 },
69 "Name": {
70 "count": {
71 "value": 1
72 }
73 }
74 }
75 }
76 },
77 {
78 "node": {
79 "aggregate": {
80 "Industry": {
81 "value": "Education"
82 },
83 "Name": {
84 "count": {
85 "value": 1
86 }
87 }
88 }
89 }
90 },
91 {
92 "node": {
93 "aggregate": {
94 "Industry": {
95 "value": "Electronics"
96 },
97 "Name": {
98 "count": {
99 "value": 1
100 }
101 }
102 }
103 }
104 },
105 {
106 "node": {
107 "aggregate": {
108 "Industry": {
109 "value": "Energy"
110 },
111 "Name": {
112 "count": {
113 "value": 3
114 }
115 }
116 }
117 }
118 },
119 {
120 "node": {
121 "aggregate": {
122 "Industry": {
123 "value": "Hospitality"
124 },
125 "Name": {
126 "count": {
127 "value": 1
128 }
129 }
130 }
131 }
132 },
133 {
134 "node": {
135 "aggregate": {
136 "Industry": {
137 "value": "Transportation"
138 },
139 "Name": {
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(Name)
2FROM Account
3GROUP BY IndustryGet Accounts and Group By Year
1query GroupAccountsByYear {
2 uiapi {
3 aggregate {
4 Account (groupBy: { CreatedDate: { function : CALENDAR_YEAR } } ) {
5 edges {
6 node {
7 aggregate {
8 Name {
9 count {
10 value
11 }
12 }
13 CreatedDate {
14 calendarYear {
15 value
16 }
17 }
18 }
19 }
20 }
21 }
22 }
23 }
24}This query provides the following response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Name": {
11 "count": {
12 "value": 24
13 }
14 },
15 "CreatedDate": {
16 "calendarYear": {
17 "value": 2022
18 }
19 }
20 }
21 }
22 },
23 {
24 "node": {
25 "aggregate": {
26 "Name": {
27 "count": {
28 "value": 5
29 }
30 },
31 "CreatedDate": {
32 "calendarYear": {
33 "value": 2023
34 }
35 }
36 }
37 }
38 }
39 ]
40 }
41 }
42 }
43 },
44 "errors": []
45}The previous query is similar to the following SOQL statement.
1SELECT Count(Name), CALENDAR_YEAR(CreatedDate)
2FROM Account
3GROUP BY CALENDAR_YEAR(CreatedDate)Get the Sum of Accounts for Combination of Type and Industry
1query aggregates {
2 uiapi {
3 aggregate {
4 Account(groupBy: { type: CUBE, Industry: { group: true }, Type: { group: true } }) {
5 edges {
6 node {
7 aggregate {
8 Industry {
9 value
10 grouping {
11 value
12 }
13 }
14 Type {
15 value
16 grouping {
17 value
18 }
19 }
20 NumberOfEmployees {
21 sum {
22 value
23 }
24 }
25 }
26 }
27 }
28 }
29 }
30 }
31}This query provides the following response.
1{
2 "data": {
3 "uiapi": {
4 "aggregate": {
5 "Account": {
6 "edges": [
7 {
8 "node": {
9 "aggregate": {
10 "Industry": {
11 "value": null,
12 "grouping": {
13 "value": 0
14 }
15 },
16 "Type": {
17 "value": null,
18 "grouping": {
19 "value": 0
20 }
21 },
22 "NumberOfEmployees": {
23 "sum": {
24 "value": null
25 }
26 }
27 }
28 }
29 },
30 {
31 "node": {
32 "aggregate": {
33 "Industry": {
34 "value": null,
35 "grouping": {
36 "value": 0
37 }
38 },
39 "Type": {
40 "value": null,
41 "grouping": {
42 "value": 1
43 }
44 },
45 "NumberOfEmployees": {
46 "sum": {
47 "value": null
48 }
49 }
50 }
51 }
52 },
53 {
54 "node": {
55 "aggregate": {
56 "Industry": {
57 "value": "Manufacturing",
58 "grouping": {
59 "value": 0
60 }
61 },
62 "Type": {
63 "value": "Prospect",
64 "grouping": {
65 "value": 0
66 }
67 },
68 "NumberOfEmployees": {
69 "sum": {
70 "value": 680
71 }
72 }
73 }
74 }
75 },
76 {
77 "node": {
78 "aggregate": {
79 "Industry": {
80 "value": "Manufacturing",
81 "grouping": {
82 "value": 0
83 }
84 },
85 "Type": {
86 "value": null,
87 "grouping": {
88 "value": 1
89 }
90 },
91 "NumberOfEmployees": {
92 "sum": {
93 "value": 680
94 }
95 }
96 }
97 }
98 },
99 {
100 "node": {
101 "aggregate": {
102 "Industry": {
103 "value": "Media",
104 "grouping": {
105 "value": 0
106 }
107 },
108 "Type": {
109 "value": "Prospect",
110 "grouping": {
111 "value": 0
112 }
113 },
114 "NumberOfEmployees": {
115 "sum": {
116 "value": 14668
117 }
118 }
119 }
120 }
121 },
122 {
123 "node": {
124 "aggregate": {
125 "Industry": {
126 "value": "Media",
127 "grouping": {
128 "value": 0
129 }
130 },
131 "Type": {
132 "value": null,
133 "grouping": {
134 "value": 1
135 }
136 },
137 "NumberOfEmployees": {
138 "sum": {
139 "value": 14668
140 }
141 }
142 }
143 }
144 },
145 {
146 "node": {
147 "aggregate": {
148 "Industry": {
149 "value": "Technology",
150 "grouping": {
151 "value": 0
152 }
153 },
154 "Type": {
155 "value": "Customer",
156 "grouping": {
157 "value": 0
158 }
159 },
160 "NumberOfEmployees": {
161 "sum": {
162 "value": null
163 }
164 }
165 }
166 }
167 },
168 {
169 "node": {
170 "aggregate": {
171 "Industry": {
172 "value": "Technology",
173 "grouping": {
174 "value": 0
175 }
176 },
177 "Type": {
178 "value": null,
179 "grouping": {
180 "value": 1
181 }
182 },
183 "NumberOfEmployees": {
184 "sum": {
185 "value": null
186 }
187 }
188 }
189 }
190 },
191 {
192 "node": {
193 "aggregate": {
194 "Industry": {
195 "value": null,
196 "grouping": {
197 "value": 1
198 }
199 },
200 "Type": {
201 "value": null,
202 "grouping": {
203 "value": 1
204 }
205 },
206 "NumberOfEmployees": {
207 "sum": {
208 "value": 15348
209 }
210 }
211 }
212 }
213 },
214 {
215 "node": {
216 "aggregate": {
217 "Industry": {
218 "value": null,
219 "grouping": {
220 "value": 1
221 }
222 },
223 "Type": {
224 "value": null,
225 "grouping": {
226 "value": 0
227 }
228 },
229 "NumberOfEmployees": {
230 "sum": {
231 "value": null
232 }
233 }
234 }
235 }
236 },
237 {
238 "node": {
239 "aggregate": {
240 "Industry": {
241 "value": null,
242 "grouping": {
243 "value": 1
244 }
245 },
246 "Type": {
247 "value": "Prospect",
248 "grouping": {
249 "value": 0
250 }
251 },
252 "NumberOfEmployees": {
253 "sum": {
254 "value": 15348
255 }
256 }
257 }
258 }
259 },
260 {
261 "node": {
262 "aggregate": {
263 "Industry": {
264 "value": null,
265 "grouping": {
266 "value": 1
267 }
268 },
269 "Type": {
270 "value": "Customer",
271 "grouping": {
272 "value": 0
273 }
274 },
275 "NumberOfEmployees": {
276 "sum": {
277 "value": null
278 }
279 }
280 }
281 }
282 }
283 ]
284 }
285 }
286 }
287 },
288 "errors": []
289}The previous query is similar to the following SOQL statement.
1SELECT Industry, Type, SUM(NumberOfEmployees)
2FROM Account
3GROUP BY CUBE(Industry, Type)See Also
SOQL and SOSL Reference: GROUP BY CUBE
j