Questions tagged [group-by]
The group-by tag has no usage guidance.
72
questions
18
votes
1
answer
10k
views
Reference fields on parent object in aggregate query
I am trying to run a group by query in Apex where I am grouping by a field on a lookup object. For example:
SELECT Account.Type, count(Id)FROM Opportunity GROUP BY Account.Type
I can run this from ...
- 2,767
15
votes
1
answer
649
views
Is Salesforce SOQL GROUP BY case sensitive for different locales?
Lets say I have records with values like 'TEST DATA' and 'test data' in some field. When I run SOQL (in Developer Console or from Apex) by user with EN locale, it arranges records with 'TEST DATA' and ...
- 153
13
votes
1
answer
416
views
Aggregate SOQL - How Many Fields Can Be Grouped?
As stated in the title, is there any maximum on the number of fields that can be passed into a GROUP BY clause? I can't find any documentation on it. For example, if I had the following query:
SELECT ...
- 150k
10
votes
3
answers
7k
views
What types of fields are groupable in a SOQL `GROUP BY` clause?
The Salesforce documentation is notably terse in describing Considerations When Using GROUP BY. The guidance provided for determining which fields can be grouped is simply:
The Field object ...
- 92.8k
7
votes
1
answer
1k
views
Any way of returning a constant (ideally null) from a SOQL "group by"?
I have some code that does a series of "group by" queries, and uses a common method (apply) to process the results:
for (AggregateResult ar : [
select PaymentSpecification__c id, Status__c ...
- 136k
5
votes
1
answer
256
views
Group By Child Objects
I'm facing some trouble to GROUP BY a SELECT with 2 Child Objects.
This is the following SELECT:
[SELECT
Id AtendId, BI_BR_Adabas__c, BI_BR_Adabas__r.BI_BR_Parceiro__c, BI_BR_Cliente__c,
...
- 166
5
votes
1
answer
2k
views
group by soql query
I have records with same values in custom field, Unique_Name__c. On a Visualforce Page, I have to display records grouped by Unique_Name__c.
Unique_Name__c is custom field on OpportunityLineItem ...
- 2,226
4
votes
3
answers
3k
views
Why am I getting group by aggregate errors in this query
I need to count the total number of orgs. Why won't this query work? I get errors not matter what I group or not group. I only put in Group by to try to placate the compiler but he's in a very bad ...
- 1,288
4
votes
1
answer
2k
views
SOQL aggregate functions do not work with Task.ActivityDate
When I try the following in the Query Editor of the Developer Console:
SELECT WhoID, COUNT(Id), MIN(ActivityDate), MAX(ActivityDate)
FROM Task
GROUP BY WhoID
LIMIT 1000
I get the following error:
...
- 486
4
votes
1
answer
10k
views
how to use group by in soql with lists
Hi friends i'm new to sfdc development I wrote a sample query to fetch opportunities related to account with some conditions try to group i getting error.
my code is:
List<Opportunity_Trial__c>...
- 1,460
4
votes
1
answer
2k
views
SOQL - how to group by CALENDAR_MONTH
I am trying to create a custom chart in a visualforce page. I am using the results of a SOQL query as my data for the chart. I have this SOQL query:
SELECT CALENDAR_MONTH(Hire_Date__c) month_number,...
- 331
3
votes
2
answers
21k
views
How to read field values from AggregateResult
I need to use group by cube to display the count of added services for each group for each package. I get an error on line 18: method does not exist or incorrect signature: void get(String) from the ...
- 81
3
votes
2
answers
6k
views
GROUP BY custom field
I'm doing this query:
SELECT
ID, Name, Email_Domain__c
FROM
Contact
GROUP BY Email_Domain__c
And I'm getting this error
MALFORMED_QUERY: GROUP BY Email_Domain__c ^ ERROR ...
- 305
3
votes
1
answer
1k
views
Do custom metadata types not support COUNT(fieldName) with a GROUP BY?
Question title covers most of it. Here's a sample query and the error I receive in response:
SELECT COUNT(Id) FROM MyCustom__mdt GROUP BY MyField__c
field Id does not support aggregate operator ...
- 1,082
3
votes
1
answer
799
views
error thrown while trying to do a group by
I'm trying to write a query that gives me the name of users and the total number of ids assigned to them.
Here I've Daily_Task__c a custom object with a lot of fields, here I'm trying to get the ...
- 41
3
votes
3
answers
2k
views
SOQL: Recent date grouped by AccountId
OBJECTIVE
I am simply trying to return the most recent activity for a given list of accounts: List<Id>. I just want 1 row for each Account ID that gets passed. For each row I need the ...
- 4,560
3
votes
1
answer
2k
views
Querying distinct logins using LoginHistory and COUNT_DISTINCT
I seem to have run in to a SOQL error that I'm hoping someone has solved before.
I am creating a dashboard for UserLogins using the LoginHistory object
The query I am trying to run is:
SELECT ...
- 317
3
votes
1
answer
1k
views
Grouped Query Cannot Use Child Relationship in Remote Action Apex Class
I am trying to count the number of account names that occur in my soql statement. Ultimately I have a map that shows all of the accounts near a technician and I want to show the number (count) of ...
3
votes
1
answer
1k
views
SUM of 1 or more fields using GROUP BY
I have got a situation where there is a filed named "Sub-Category" which is dependent on its master "Category", 3 check boxes and a formula field. So, when I select a "Category" (for ex. X), then a "...
- 931
3
votes
1
answer
2k
views
SOQL: Get most recent amount grouped by a parent and a type
Should be simple had it been SQL. But. I would like to get the amount field value that belongs to the most recent month available for that parent and that type.
Data:
Parent_Id__c Type__c ...
- 3,458
2
votes
1
answer
818
views
Group by part of string in SOQL
Is there any way to Group By part of string in SOQL.
For example something like:
Select Id, Name from Object__c group by Name.substringBetween('.','.')
So query by part of a string field and not ...
- 165
2
votes
1
answer
1k
views
Use SOQL aggregation result to count number of related objects
I have a fun puzzle here, and would like to see if anyone has a better solution. There are 3 objects: Account, Infusion__c, Patient__c. Infusion__c is a child of Accounts, and Infusion__c has a ...
- 111
2
votes
2
answers
2k
views
Salesforce group by
I want to use group by from salesforce query :
SELECT Candidat__c, Commande__c,Statut__c,IsDeleted, TypeContrat__c
FROM Candidature__c
WHERE Candidat__c = '0032000001GAPoXAAX'
AND Statut__c = '...
- 123
2
votes
2
answers
10k
views
Formula field cannot be grouped in SOQL GROUP BY query call
I have a Pipeline__c object with fields Segment__c, Status__c, and Sales_Date__c (among many other fields).
I also have a Sub_Pipeline__c object which is related to Pipeline__c. Inside I have the ...
- 195
2
votes
1
answer
8k
views
How to get unique values of two fields in one SOQL?
I have a custom object named "Performance" in which there are two text fields named "Main Category" and "Sub Category". Here, I would like to first extract the unique values of Main Category from all ...
- 197
2
votes
1
answer
309
views
split before GROUP BY soql
I have a field which has the values in the format [5 digits-3 digits]
12345-123
34646-345
12345-133
I want to Group BY this field with the just the 5 digit number only (not the complete field). How ...
- 4,066
2
votes
1
answer
1k
views
GROUP BY / ROLLUP Query Rows
Why does using GROUP BY ROLLUP multiply the number of query rows returned by the number of fields you are rolling up + 1?
Example:
I have 3408 records in the database. If I run:
[SELECT Sex__c, ...
- 21
2
votes
1
answer
74
views
Limit results from list
I have a helper class in which I'm trying to create a new record using information from various other related records that are gathered using maps and lists.
My problem is that I'm trying to build a ...
- 191
2
votes
1
answer
153
views
Group By Cube on Related List
I need to do a group by cube on a related list. What I want is to display a list of added services groups and the distinct count across all completed inspections by package, and the matrix should have ...
- 81
2
votes
0
answers
269
views
GROUP BY Clause in SOSL (FIND instead of SELECT)
I am looking for the equivalent of the GROUP BY statement but then for a FIND query.
I want to do a search on all fields of ContentVersion by a keyword and I want it to return a list of all values in ...
- 574
1
vote
1
answer
3k
views
SOQL: Aggregate, Grouping By and WHERE Clauses not working
This query
SELECT Reason, COUNT(Id)
FROM Case
WHERE RecordType.DeveloperName = 'Problem' AND AccountId = 'xxxx'
AND GROUP BY Reason
is giving me
Unknown parsing error:
But when I run the ...
- 437
1
vote
2
answers
177
views
Access SOQL results by grouped field
I'm attempting to aggregate all ObjectPermissions at the user level for a user access review (audit) application. This application's central function is to show an end-user (a reviewer) their reviewee'...
- 502
1
vote
2
answers
4k
views
Marketing Cloud -SQL Group BY
I have a table and I need to run some calculations on it for reporting purposes.
I want to add a row that measures counts by that month. And I'm attempting to use the following statement:
SELECT
...
- 13
1
vote
1
answer
893
views
SOQL record with highest value, GROUP BY not working
We have an object called "Compensation__c". This object contains the compensation a partner receives for selling a "Product2".
"Compensation__c" has a start date. Before this date this compensation ...
- 409
1
vote
2
answers
4k
views
Displaying month/year from createdDate in SOQL query
I am very new to Salesforce and struggling to get a query to return the data in the format I require - i.e pushing the results through Conga composer to feed a chart in a powerpoint template. The ...
- 13
1
vote
2
answers
1k
views
CPQ - Template Section - Group Field - Sorting
I am in the process of using the Group Field in the Template Section object to Group a Line Items section, and am hitting an issue.
It seems to be grouping in alphabetical order of the picklist ...
- 11
1
vote
1
answer
64
views
StandardSetConnector Aggregate query grouping
I have a pageblock table with the following columns:
Company, Package, Type, Revenue, Age, Users, Total Usage Actions, Top 3 Modules.
All of the fields apart from "Total usage actions" and "Top 3 ...
- 11
1
vote
1
answer
351
views
SOQL query aggregation
I need to provide statistics to the users in my VF Page, showing the total of records they own where the status is New, In progress, Approved, Pending, Converted
I have tried the below query in my ...
1
vote
1
answer
126
views
A field isGroupable is FALSE but GROUP BY is still working
I have enabled the Shield Platform Encryption and encrypted the BillingAddress field of the Account sobject.
When checking the describe field result of the BillingCountry (sub-field of the ...
- 79
1
vote
0
answers
62
views
Visualforce table for aggregate result grouped by CreatedBy.Name
I'm trying to create a custom report table in Visualforce based on an aggregate result retrieved by the SOQL:
SELECT CreatedBy.Name, Description, SUM(Value1), SUM(Value2), SUM(Value3) FROM Object__c ...
- 11
1
vote
0
answers
150
views
Group by, Complex query
I am trying to do a query that engloves two different querys, the idea is spend just one soql query instead two but I don´t find the way.
AggregateResult[] results = [
SELECT ...
- 375
1
vote
0
answers
40
views
Salesforce google charts group by aggregate query not pulling appropriate results
Set<Id> realBy = new Set<Id>();
for (Projects__c vP1 : [SELECT Id,Name,(SELECT Id, Total_Value__c FROM Proj_Upd__r
WHERE ...
- 43
1
vote
1
answer
250
views
Report Grouping by Date
I'm relatively new to SF but gaining steam quickly. I've got Opportunities that include a contract start date and contact end date and I am trying to create a report that summarizes who we have ...
- 11
1
vote
1
answer
172
views
Group by: Combining two groups
I need help with this query. It returns me with 3 groups: Closed, Waiting and In progress. However I want to combine the Waiting and In progress groups to see the Closed vs not closed. How can I do ...
- 15
1
vote
2
answers
501
views
using group by in my query
I have the following query that adds each record to a list that is displayed in as dataTable on a Visualforce page.
for (MyObject__c obj : [SELECT Date__c, Stage__c, Members__c, Type__c, ...
- 2,435
0
votes
1
answer
4k
views
Group By in Soql
I need to group data on the basis of two fields "description" and "createdDate" in soql.
Select Subject, WhatId, what.name, Owner.name, Owner.Id, IsVisibleInSelfService, ActivityDate, ...
- 954
0
votes
1
answer
354
views
SOQL check if unique value exists and throw error message
I want to check if all the records have the same values for "Group__c" field and throw a message if the values are different.
Example data:
If all the records are of the same group(Group-A ...
- 25
0
votes
1
answer
348
views
marketing cloud - how to join my audience with subscribers having a distinct value [closed]
I have an audience in a table called wtab which contains a column mail,
and I want to join it with the subscribers data view without duplicates.
when I have one 1 column I can use the DISTINCT keyword ...
- 340
0
votes
1
answer
41
views
List has more than 1 row for assignment to SObject error
Help please, I test the SOQL query in developer Console, and work as expected But, i get error message in the VF
My Controller is:
public class InventoryReport {
public List<...
- 111
0
votes
1
answer
745
views
SOQL Group By Query
I am trying to include an additional currency field in the below Group By query, but receive SOQL error.
"Field must be grouped or aggregated".
Is it possible to add a Currency Field to a Group By ...
- 89