12
votes
Accepted
SOQL AggregateResult Query throws error: System.ListException: Row with null Id at index: 0
You need to alias some field to Id in order for the Map<Id, SObject>(List<SObject>) constructor to work.
SELECT
OpportunityLineItemId Id,
MIN(ScheduleDate) Starts
FROM ...
- 151k
7
votes
Accepted
SOQL SUM() function not calculating the correct value
See Querying Currency Fields in Multi-Currency Orgs. Any time you use an aggregate function, it is always in the organization's currency.
You can’t convert the result of an aggregate function into ...
- 502k
7
votes
Accepted
What is more effecient , using SOQL AVG() or to query all records and calculate their average
Well, the easiest way to figure that out is to do some benchmarking. Adrian Larson's Apex Limits Profiler can help out with that, but I'm feeling a bit lazy, and this seems like a simple enough test.
...
- 64.1k
6
votes
how to add lastmodifieddate in aggregate list
If you want the 5 most recent Account records:
Remove the comma (,) after the Name field in your SELECT clause.
Remove the GROUP BY clause entirely if you want individual records.
In tandem with the ...
- 151k
6
votes
Accepted
Why I can't get the ID from group by along with Count(ID)?
If you want to count how many records match each Application_Prefix__c, then you should remove Id from your SELECT clause:
SELECT COUNT(Id), Application_Prefix__c FROM Test__c GROUP BY ...
- 151k
5
votes
Accepted
SOQL aggregate functions do not work with Task.ActivityDate
I have found a workaround by creating a custom field of type date under the Activity object called ActivityDate__c. Thus we can reformulate the initial query as
SELECT WhoID, COUNT(Id), MIN(...
- 486
5
votes
Accepted
get MIN date with Id from aggregate result query
In this specific case, it'd be a lot easier to order by date ascending and limit by 1:
MyObject__c minDOBRecord = [SELECT Id FROM MyObject__c WHERE Id = :setIds ORDER BY DOB__c ASC LIMIT 1];
In real ...
- 502k
5
votes
Accepted
Count the number of details in master-detail relationship
use count aggregate function. You can have Set<Id> masterIds = ... in this case you can use in SOQL operator.
List<AggregateResult> result = [
select count(Id)cnt, Master_Type__c
...
- 18.8k
5
votes
Accepted
Select aggregate into Integer using Database.query()
Yes. You can query using Database.countQuery(query)
String QueryString = 'SELECT count() FROM Account';
Integer countAccounts = Database.countQuery(QueryString);
5
votes
Accepted
SOQL: Aggregate, Grouping By and WHERE Clauses not working
Your problem is the last AND.
The right query is:
SELECT Reason, COUNT(Id)
FROM Case
WHERE RecordType.DeveloperName = 'Problem' AND AccountId = 'xxxx'
GROUP BY Reason
- 13.2k
4
votes
Accepted
SOQL aggregate query limit [Updated for Summer18]
SOQL count query now counts as 1. So you can count all the things.
https://releasenotes.docs.salesforce.com/en-us/summer18/release-notes/rn_apex_soql_count_limits.htm
- 8,706
4
votes
Inconsistency with error "Aggregate query has too many rows for direct assignment"
Salesforce, from time to time, uses some not-so-clear wording and terminology. A perfect example is "Aggregate" queries. You might think that an "Aggregate" query is one that uses Aggregate functions ...
- 64.1k
4
votes
Accepted
Errors with AggregateResult
Declaring an Apex class with the same API name as an Sobject will lead to unexpected results like this one. Your Visualforce page is trying to bind to what it thinks is an Sobject ...
- 93.7k
4
votes
Accepted
System.LimitException: Too many query rows: 50001 - How to solve it with a batch?
On the face of it, a Batch on Contract would be appropriate. I would write the batch to iterate the Contracts and have the execute perform the aggregate query and related processing. Your aggregate ...
- 38.1k
4
votes
Aggregate Queries: Can I return values where GROUP BY size is greater than 1?
The HAVING clause works perfectly fine for this requirement. Just remember it goes after GROUP BY.
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING count(Id) > 1
- 151k
4
votes
Accepted
How can I prevent a ContentDocument being deleted when it is linked to a record meeting certain criteria?
It should be as simple as:
AggregateResult[] results = [
SELECT COUNT(Id) recordCount, ContentDocumentId documentId
FROM ContentDocumentLink
WHERE ContentDocumentId IN :contentDocumentIds
...
- 502k
3
votes
Accepted
Finding MIN(1) date in a data extension - error message about aggregate functions
When you use an agregate function (e.g COUNT, MIN, AVERAGE, MAX), you have to finish the statement with a GROUP BY clause and include any column that is not agregrated.
In your case it would be
...
- 6,613
3
votes
Accepted
how to add lastmodifieddate in aggregate list
If you do need to group the Accounts by name for some reason, and you only want to do this for the 5 accounts that were most recently modified, I think you can use one of the following two options:
...
- 5,623
3
votes
Accepted
Aggregate query in trigger
This query certainly could be made non-selective, if either ownerIdSet or oppStatusSet contain a null value. Another issue to worry about is ownership skew. If you have one user who owns tens of ...
- 151k
3
votes
Accepted
Aggregate result
you did a typo in VF page. !{summary.BWS} : !{summary.JZ}. exclamation mark !should be after brace {.
Correct way {!summary.BWS} : {!summary.JZ}
<apex:repeat value="{!Summaries}" var="summary"&...
- 18.8k
3
votes
Accepted
Aggregate query with filter in batch
As the [doc states(https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_database.htm?search_text=getquerylocator), the argument to a Database.getQueryLocator ...
- 73.3k
3
votes
Accepted
APEX Aggregate Query - Query to get Sum of Amount of all opportunities of Account when stage is Closed Won
It gives individual amount because you're grouping by Opportunity Id, so you are not grouping at all.
You should use just AccountId in both SELECT and GROUP BY clause.
SELECT AccountId, SUM(AMOUNT) ...
- 9,567
2
votes
Accepted
Why is my aggregate query returning no rows when called during test?
You're running into the Isolation of Test Data from Organization Data in Unit Tests. In other words, by default, unit tests can't see live data. This makes your unit tests more reliable, because they'...
- 502k
2
votes
Why is my aggregate query returning no rows when called during test?
Remember that when running tests they don't by default see your data. You either have to create the data for the test (the best option) or run with @isTest(SeeAllData=true).
- 3,068
2
votes
What makes a field not groupable
For those people who come across this old but still relevant question, see the more complete community wiki answer here
- 121
2
votes
Accepted
Ordered field must be grouped or aggregated
Since you are not grouping by that field, you can only order by an aggregation of that field. For example you can use ORDER BY MAX(DateField__c) but not ORDER BY DateField__c.
- 151k
2
votes
Incompatible key type object for Map<id,account>
The dynamic get method returns an Object, so you have to cast it to a specific type (Id in this case):
Incorrect
accountMap.get(ar.get('Target_Company__c' ))
Correct
accountMap.get((Id)ar.get('...
- 151k
2
votes
What is more effecient , using SOQL AVG() or to query all records and calculate their average
Yeah it is always feasible to use aggregate query specially if you have large data set. If you are using normal SOQL query then you can hit following limitations like CPU Time limit, Heap Size. Also ...
- 697
2
votes
Accepted
Get count of records from a child object which has a reference field to a parent object
There's normally two ways to do this in SOQL, but using an aggregate query from the child to the parent won't work in this case because you're looking for zero OR zero active clients.
(The aggregate ...
- 93.7k
2
votes
Accepted
Aggregate Query issue using Tooling API / Partner/SOAP API with C#
The Response structure of SELECT Count() and SELECT Count(Id) is bit different and that why it causes the casting to fail.
Response of SELECT COUNT(Id) in tooling api Query.
<?xml version="1.0" ...
- 36.4k
Only top scored, non community-wiki answers of a minimum length are eligible