Questions tagged [aggregate]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
19
votes
3answers
11k views

System.QueryException: Aggregate query does not support queryMore()

I am trying to deploy around 80 Components which include Apex Class,Trigger and Visualforce Pages. But it is failing due to below exception, MyTrigger: execution of BeforeInsert caused by: System....
14
votes
1answer
6k 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 ...
13
votes
1answer
308 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 ...
11
votes
4answers
12k views

SOQL aggregate query limit [Updated for Summer18]

I'm trying to use aggregate queries to save work and script statements. The object has a field for each month representing energy usage for that month for a building. In situations where a building ...
11
votes
2answers
9k views

Can I assign Aggregate SOQL Result directly to variable?

Salesforce documentation suggests that aggregrate soql results must first be assigned to an instance of the AggregateResult class, as shown below: AggregateResult[] groupedResults = [SELECT AVG(...
10
votes
2answers
8k views

What makes a field not groupable

I'm trying to use an aggregate query, but one of the fields I want to group by is not groupable (saw it in the soql error and then verified groupable=false in the metadata). It's just a number field ...
10
votes
3answers
22k views

How to use Count() in SOQL

I have a custom object. This custom object has a field Cabin_Type__c, which has values of Twin and Luxury. There is another field Deck_Type__c which is just the number of the deck such as 1,2,3,etc. ...
9
votes
1answer
417 views

Group by Rollup: Visualforce Remoting Exception: Too many query rows: 1000001

I have 557,029 Foo objects in my org. I want to count how many of each kind of picklist value there are within these: Type 1 - 100,000 Type 2 - 60,000 Type 3 - 40,000 Type N - N I thought I could ...
6
votes
1answer
325 views

SOQL AggregateResult Query throws error: System.ListException: Row with null Id at index: 0

Background I have this Apex SOQL query: myMap = new Map<Id, AggregateResult>([SELECT OpportunityLineItemId, MIN(ScheduleDate) Starts ...
6
votes
2answers
213 views

how to add lastmodifieddate in aggregate list

AggregateResult[] groupedResults; if(Schema.Account.getSObjectType().getDescribe().isAccessible()) groupedResults = [SELECT Name, FROM Account where Name like :sql or name IN:sql1 Group By Name ...
6
votes
1answer
115 views

Batch Apex - Need help

This is my first time trying to use batch apex. I copied the example from Trailhead and attempted to move my class code into it. I don't think I have the aggregate query coded correctly but I don't ...
6
votes
1answer
2k views

Display the related aggregated value on a VF page

We have a custom VF page that we use in our Org for time entry, and on that page employees select a project, iteration, and enter their time for that week. Note: some employees might be working on ...
5
votes
2answers
2k views

Remote Objects and Aggregate Function

Is it possible to call count() with remote objects?
5
votes
3answers
2k views

Using Aggregate SOQL With Large Amounts of Data (> 500k records)

I am facing a issue about aggregate soql. My basic data object more than 500,000 record. But, i want to aggregate this object data. When i execute following data, SFDC will report the error for me. ...
5
votes
2answers
454 views

SOQL Query of lookup relationship

I have an SObject called Team_Member__c It has a lookup field Manager__c pointing to Team_Member__c, so the Team_Member__c SObject is related to itself. The relationship represents a management ...
5
votes
2answers
6k views

Display an aggregate result on a visualforce page

I try to display on my visualforce page the result of a soql query (an aggregate result precisely). Here is my controller (a partof it) : public List<AggregateResult> somme {get; set;} public ...
5
votes
1answer
369 views

Aggregate query fails when MyField__c exits in both package (NS_MyField__c) and customer org (MyField__c)

This is really a strange bug. Inside my managed package I have a NAMESPACE_CustomObject__c with a NAMESPACE_CustomField__c. In one of my packaged classes I have an Aggregate Query String soql = '...
5
votes
2answers
2k views

Grouping in Visualforce Charts

I have created a visualforce charting barSeries and it renders the results that I want aside from products with values of zero for the month. The chart is a repeat business report that is meant to ...
5
votes
1answer
519 views

Echoing the Counts of each record through PHP using SOQL and GROUP BY [closed]

To start, this is the query: $query = "SELECT LeadSource, COUNT(id) Total FROM Lead WHERE CreatedDate > 2013-08-26T00:00:00Z AND CreatedDate < 2013-08-...
4
votes
2answers
5k views

How can i get the id of the max record?

Here is a situation where i need to get the last date of apppointment for a contact and i also need the id of the last appointment date. All appointments are stored in custom object and has a lookup ...
4
votes
3answers
1k views

Aggregate Values with Limits and Orders

Basically, what I am looking to accoplish with the SOQL Query, is to return the average value of the Last N (20) SAT scores from the Case object. The query would essentially look like this: Select ...
4
votes
1answer
8k views

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

I am trying to retrieve a list of LoginHistory for a bunch of users, but only want the most recent LoginHistory for each user. I have been trying this query, but I keep getting an unknown parsing ...
4
votes
1answer
118 views

Apex aggregrateresult error

Any help why I'm unable to add this? List<AggregateResult> revenue = [select SUM(Daily__c) rev, Client__r.Name from Commissions__c]; Integer totalMonthlyRevenue = 0; for ( AggregateResult i :...
4
votes
3answers
179 views

SOQL: Returning results where detail (M-D) count is at least 5

I'm trying to set up a query that will return which contacts have over 5 deals with us. We have a custom sObject which is (terribly) named, contacts_deals__c which serves as an intermediary table ...
4
votes
1answer
725 views

SOQL SUM() function not calculating the correct value

I have a SOQL query that I'm trying to use the SUM function with. My issue is that the SUM function gives me a totally different result than if i just query the rows and add up the amounts Query 1 - ...
4
votes
2answers
2k views

What is the coalesce() eqivalent?

Much like this post here: SOQL: Avoid count with NULL results return 0 (zero) instead I have an instance where I am using aggregate functions like COUNT() and MAX() on a collection of Opportunities. ...
4
votes
2answers
1k views

How can you aggregate a checkbox field with SOQL?

I need to get the average completion of a task which is recorded using a checkbox field. Since that is stored as TRUE and FALSE instead of 1 and 0 it's proving a bit difficult. I would have liked to ...
4
votes
1answer
1k views

AggregateResult for a multi-select picklist

I am doing an AggregateResult SOQl query on different picklists on a Contact. List<AggregateResult> results = [select LeadSource, COUNT(id)cnt from Contact where LeadSource != null group by ...
4
votes
1answer
2k views

activity aggregate relationships only allow security evaluation for non-admin users when a single parent record is evaluated

I am getting the following error when I run a Salesforce SOQL query with a standard account. Query: SELECT ( SELECT Id, Subject FROM OpenActivities ORDER BY ActivityDate ASC , LastModifiedDate DESC ...
4
votes
1answer
474 views

Efficient way to perform grouping without using aggregate SOQL queries

I have an aggregate query that groups my custom object records by 4-5 fields and then returns a sum of currency fields in each grouping. I have multicurrency enabled so this sum of the currency field ...
3
votes
2answers
3k views

How to sort Wrapper class?

Am working with Aggregation on an object and i have specified count field in Order By Clause. Am loading aggregate results into wrapper class. Here am not getting the values in specified order. In ...
3
votes
2answers
1k views

SObject Exception: Invalid field for Opportunity

I am attempting to set a flag on the Opportunity Header based on an Aggregate Query of the Opportunity Line Item object, and I keep getting an SObject Exception. My code and the error log are below, ...
3
votes
1answer
7k views

SOQL aggragated query for unique values

I have an object which captures searches made by users. It has a Searc_Phrase__c field. Each record captures one search attempt - the phrase, the use id and the date. I want to query how many times ...
3
votes
4answers
3k views

Non-selective query against large object type in trigger

The Alert__c object has a Rule ID field, which is just text, and a lookup to the Master__c object. I have a trigger on Alert__c, which when runs after insert, update or delete. It collects the Master ...
3
votes
1answer
147 views

For loop run just once

I have seven different recordtypes, but it seems that the first "for" (FOR (RecordType RType : RTypeList) loop run just once. The debug log show that the sql find 7 rows. 09:22:47.089 (89771210)|...
3
votes
1answer
111 views

Select aggregate into Integer using Database.query()

So we all know we can put an aggregate directly into an Integer, like this: Integer countAccounts = [SELECT COUNT() FROM Account]; Is it possible to do the same thing, using Database.query()? ...
3
votes
1answer
941 views

Most efficient way to transform AggregateResults to String, Object Maps

Question: What is the most efficient way to transform aggregate results to string object maps? Constraints: The keys of the aggregated results are unknown. The transformation has to be performed as ...
3
votes
1answer
2k views

Too many SOQL queries: 101 in apex class. How to count results?

List<tempcon1> listoftemp; CheckContactController chk = new CheckContactController(); public List<tempcon1> getconValues() { listoftemp = new List<tempcon1>(); for (...
3
votes
2answers
422 views

Inconsistency with error “Aggregate query has too many rows for direct assignment”

I have a Box__c record with 100 Doors__r, 104 Motors__r, and 0 Wheels__r. ISSUE #1 I am getting the error System.QueryException: Aggregate query has too many rows for direct assignment, use FOR ...
3
votes
5answers
1k views

How to I use SOQL to aggregate values and only return the largest sum?

I want to do something like this, but you can't nest these particular functions: select max(sum(Number__c)), User__r.Name, Category__c group by User__r.Name, Category__c If my result set ...
3
votes
1answer
49 views

Aggregate Query issue using Tooling API / Partner/SOAP API with C#

I wrote a utility that cycles through a bunch of orgs and pulls out information. I'm using the Partner SOAP API and C# to do this. Now, I'm trying to get some additional information that is only ...
3
votes
1answer
899 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 ...
2
votes
2answers
637 views

What is more effecient , using SOQL AVG() or to query all records and calculate their average

Is it efficient to calculate AVG using aggregate function, using a group by clause. Like: (Select Designation__c,AVG(Salary) from Employee__c GROUP BY Designation__c) Or is it efficient to fetch all ...
2
votes
1answer
680 views

Find AggregateResult Fields Dynamically

I ran across some code a while back to find fields within an AggregateResult dynamically to do some processing irregardless of what was in it. AggregateResult results = [SELECT COUNT_DISTINCT(...
2
votes
2answers
2k views

Number of counts

I have one student object from that i am displaying values in visualforce page but i want to display number of males and females in that visualforce page i am not getting count of gender i am using ...
2
votes
1answer
975 views

get MIN date with Id from aggregate result query

the below query works but it does'nt what I'm expecting and the problem with the below query is that it gives me all the record where as; what I want is to get the minimum dob with Id. here is my ...
2
votes
2answers
699 views

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

I have a custom object customobj__c with the following fields: contact__c (lookup), responded__c (boolean), click_count__c (integer) ... I can aggregate the click_count using: SELECT contact__c, ...
2
votes
1answer
1k views

convert aggregate result to list

I'm passing a list to a serialize JSON string value back to a Visualforce page. Traditionally I can easily pass back a List to the JSON. However, my SOQL is an aggregate. I need to be able to ...
2
votes
1answer
4k views

Put AggregateResult in Map

I'm trying to put my AggregateResult in a map so that I can reference it later in my code. trigger NoOfLiveProducts on Account (after insert, after Delete, after update) { map<Id,Integer> ...
2
votes
1answer
981 views

SOQL Query to count only Accounts with Opps in both this year and last year?

So rather than create an Apex class, run multiple queries then write some code to figure out the answer, I thought I'd take a stab at writing a complex query that would get me the answer I'm looking ...