21

What happens if you alias the field, does that work? AggregateResult[] groupedResults = [ SELECT Account.Type type, count(Id) oppCount FROM Opportunity GROUP BY Account.Type]; // snip System.Debug(ar.Get('type')); Notice that if you are doing an aggregation (GROUP BY) you can create an alias for both the summarized field and for other fields. ...


16

Why not with just a subquery? SELECT Id, Name, (SELECT Id, StartDate__c FROM Appointments__r ORDER BY StartDate__c DESC LIMIT 1) FROM Contact Keep it simple, aggregate functions "waste" query rows (for example COUNT() will return only 1 row but will use up N rows from the limit)


15

You cannot simplify the query, but you can the assignment... Decimal avgAmount = (Decimal) [SELECT AVG(Amount)aver FROM Opportunity][0].get('aver'); Normally I would have said this was a little unsafe, since your always assuming you have one item in the result array the aggregate query returns. However I've just tested it and even if the object has no ...


15

I think that the issue here is that an aggregate query is considered to have accessed every record included in the aggregation. Thus the 50000 row limit isn't the number of records returned by the query, its the number of records 'touched'. As you have a clause of 'LIMIT 50000', you are asking for a maximum of 50000 aggregates to be returned by the query ...


14

Implement the Comparable interface: https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_comparable.htm To do this you implement Comparable: public class QLWrap implements Comparable Then you need to implement a compareTo method: global Integer compareTo(Object compareTo) { //I don't know what criteria you want to sort on QLWrap ...


14

The maximum is 32 groupings. If you exceed this, you receive the following error: System.QueryException: Group By must contain 32 fields or less This used to be in the documentation, but I can't seem to find it now. Proof Set<String> fields = lead.sobjecttype.getdescribe().fields.getmap().keyset(); Database.query('select id from lead group by '+...


13

You would want to use an AggregateResult query here. This is obviously just some shorthand code as I really don't know your object or field names so you will have to adjust those to fit in order for this to compile for you AggregateResult[] ARs = [SELECT count(Id) myCount, Cabin_Type__c, Deck_Type__c FROM cruise_ deck_Cabin_paln__c GROUP BY ...


11

I'm not 100% sure what makes it "groupable". The Considerations When Using GROUP BY does mention it: Some object fields have a field type that does not support grouping. You can't include fields with these field types in a GROUP BY clause. The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can ...


10

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 OpportunityLineItemSchedule WHERE ... GROUP BY OpportunityLineItemId As your query is currently written, a given AggregateResult will only allow you to get three "...


8

Have you tried something like below? SELECT Searc_Phrase__c, COUNT_DISTINCT(UserID) FROM CustomObject GROUP BY Searc_Phrase__c


7

I updated my code as below and it worked for me. Integer recordCount = [select count() from XYZ__c where ABC__c in :oppMap.keySet()]; Integer sizeBlock = recordCount/2000 + (math.mod(recordCount,2000)!=0?1:0) ; Set<Id> setAppIds = new Set<Id>(); //Iterate aggregate result with blocks of 2000 to avoid ...


7

It appears that you can not group by custom number fields and not the ability to group by formulas is a request on the ideaexchange so you will need to create a workflow rule that copies the number to a text field. Note that most standard number fields cannot be grouped by as well but a few like account.numberofemployees can be.


7

The problem is probably that you re-initilaise the Summaries list every time round the loop to an empty list so you end up with only 1 item in the final list. This is probably what you want: Summaries = new List < Summary > (); FOR(RecordType RType: RTypeList) { AggregateResult[] Results = [SELECT Account.Owner.Name accOwner, RecordType....


7

Believe it or not, I was very, very close. JSON.deserialize may not be able to provide me a Map, but JSON.deserializeUntyped can. Once I found out I could utilize that, I came up with the following code: AggregateResult results = [SELECT COUNT_DISTINCT(ParentId) FROM Account]; Map<String, Object> aggregateResultMap = (Map<String, Object>)...


7

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. My test code is as follows: Decimal average; // Profiling using SOQL aggregate function Decimal startTime = Limits.getCpuTime(); DateTime startDT = DateTime....


6

Instead of using the having Master__c IN :MasterIds; Try adding this filter to the where clause where .... And Master__c in :MasterIds; Right now, as I read this, you're essentially selecting through all of the alert__c records with those rules, and there are a lot of them (since I'm betting the Rule_ID_c field is external, but not unique. After ...


6

I would look into Aggregate Results queries. Without knowing too much about your requirements , here is a simple example below list<aggregateResult> aggResults = [SELECT MAX(Amount)myMax FROM Opportunity]; System.debug('Max is: ' + aggResults[0].get('myMax')); For more on AggregateResult queries, see the links below http://www.salesforce.com/us/...


6

You can't access fields on instances of AggregateResult directly. Use the get method instead, along with Integer.valueOf because the compiler won't like you adding an Object to an Integer. Give this a shot: totalMonthlyRevenue += Integer.valueOf(i.get('rev'));


6

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[,...] [USING SCOPE filterScope] [WHERE conditionExpression] [WITH [DATA CATEGORY] filteringExpression] [GROUP BY {fieldGroupByList|ROLLUP (...


6

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.


6

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 above, assign to a List<Account> instead of List<AggregateResult>. So change it to: List<Account> mostRecent = [ SELECT Name FROM ...


6

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 Application_Prefix__c You can't really get both the individual Id values and the count per prefix in a single query. If you wanted to get the counts using post-processing, ...


5

One of our developers Chris Peterson has a case raised with Salesforce 09290363, in it he is currently discussing with the support team if it is a bug or not. Our feeling is it is a bug and agree with you though it maybe an edge case in some respects, it also could be quite tricky to get out from, especially if the duplicate name was introduced as part of an ...


5

You can override the default remote object operations, programmatically get a count, add it to the result, and then use it in the Visualforce page. In this case, to get the count you could override the retrieve method to get a count of whatever you need and store it in a property. In the Visualforce callback you can access the property in the event.result ...


5

Before you do anything else, please remove the aggregate query from within the loop. You're doing a query that will by definition return zero records because you're specifying that the Id of the Contract is the AccountId, which is impossible. Instead, change your WHERE clause to AccountId = :a.Id. But you're not done there... Iterate through all the ...


5

Suggested solution: Convert a list of aggregate results to a Json string and deserialize the string as an object. List<Map<String, Object>> getAggregateResultAsMap(List<AggregateResult> aggrs) { String js = Json.serialize(aggrs); // AggregateResult as Map List<Map<String, Object>> result = new List<...


5

I think you will have to create a rollup summary field in contact object to show the count of deals. This is needed only if you need to get it in one query. Otherwise you can go for apex code with query and then filtering by iteration. Then you can run direct query on contact itself. SELECT name,rollup__c FROM Contact WHERE rollup__c > 4


5

If you are looking for just total value. you can remove grouping and do like below, AggregateResult[] sumResult = [Select SUM(ProductPrice__c)totalUnitPrice from Inventory__c where OppLineItemId__c = :this.selectedOPLIId and ...


5

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 SQL, it'd be easier to do this with a join, but we can't do that in SOQL, so this is the most efficient way to do this. The GROUP BY option doesn't limit how ...


5

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 the user’s currency by calling the convertCurrency() function. If a query includes a GROUP BY or HAVING clause, currency data returned by using an aggregate ...


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