Skip to main content
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 ...
Adrian Larson's user avatar
  • 150k
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. ...
Derek F's user avatar
  • 62.4k
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 ...
sfdcfox's user avatar
  • 495k
6 votes

How can I aggregate a Boolean value for a column in SOQL?

How about SELECT contact__c, responded__c, count(id) FROM customobj__c GROUP BY contact__c, responded__c This would give up to two rows per contact, one row with true count and one with false count.
Doug B's user avatar
  • 11.5k
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 ...
Adrian Larson's user avatar
  • 150k
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 ...
Adrian Larson's user avatar
  • 150k
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 ...
sfdcfox's user avatar
  • 495k
5 votes
Accepted

How to use ORDER BY and GROUP BY together in the same query

You must put GROUP BY before ORDER BY. Take a look at SOQL SELECT Syntax: SELECT fieldList [subquery][...] [TYPEOF typeOfField whenExpression[...] elseExpression END][...] FROM objectType[,...] ...
Adrian Larson's user avatar
  • 150k
5 votes

Incompatible types since an instance of List is never an instance of AggregateResult

When you execute a query that includes a GROUP BY term it is always an array (list) of AggregateResult objects that is returned - see Working with SOQL Aggregate Functions. So your method will have ...
Keith C's user avatar
  • 136k
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(...
catalandres's user avatar
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 ...
Oleksandr Berehovskyi's user avatar
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);
Hemavantha Rajesh Varma Mudunu's user avatar
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
Sebastian Kessel's user avatar
4 votes

Batch Apex - Need help

I don't believe you want to run this type of aggregrate query in a batch class. Instead, you'll want to sort the data yourself in your method. The query locator splits the records returned into ...
crmprogdev's user avatar
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
Shane McLaughlin's user avatar
4 votes
Accepted

convert aggregate result to list

Just use the AggregateResult directly. If you decide to serialize it, your data would look like this: [    {       "attributes": {          "type": "AggregateResult"       },       "Name": "Account 0"...
sfdcfox's user avatar
  • 495k
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 ...
Derek F's user avatar
  • 62.4k
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 ...
David Reed's user avatar
  • 93.2k
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 ...
Phil W's user avatar
  • 36.8k
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
Adrian Larson's user avatar
  • 150k
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 ...
sfdcfox's user avatar
  • 495k
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 ...
Adrian Larson's user avatar
  • 150k
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: ...
smukov's user avatar
  • 5,593
3 votes

Extracting date year from aggregate result query

Try to cast it to Date after you are fetching the result, such as: Date d = (Date)ar.get('expr0'); Integer maxyear = d.year(); system.debug('****intmax'+maxyear);
Marc Zaharescu's user avatar
3 votes
Accepted

Aggregate Values with Limits and Orders

The query you issued is not correct, even in SQL. If you're going to order by ClosedDate, then you must also group by it. From the Aggregate Functions documentation: You can't use a LIMIT clause ...
Sebastian Kessel's user avatar
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 ...
0xsegfault's user avatar
  • 6,603
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"&...
Oleksandr Berehovskyi's user avatar
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 ...
cropredy's user avatar
  • 71.9k
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
Mike's user avatar
  • 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.
Adrian Larson's user avatar
  • 150k

Only top scored, non community-wiki answers of a minimum length are eligible