Questions tagged [aggregate]
The aggregate tag has no usage guidance.
162
questions
-2
votes
1
answer
44
views
Aggregate Queries: Can I return values where GROUP BY size is greater than 1?
If I say
SELECT count(id), field_one__c, field_two__c
WHERE field_once__c > 1 AND field_two__c < 6
GROUP BY field_one__c, field_two__c
could I have it only return groups where the size ...
- 1
0
votes
0
answers
65
views
In Salesforce CPQ, Are the Values of Summary Variables Cached, or Are They Retrieved in Real Time on Calculate?
In Salesforce CPQ, most standard Price Rules are pre-cached in the background during Quote Line Editor (QLE) load to minimize the performance impact.
But certain features (like Lookup Queries) are not ...
0
votes
1
answer
185
views
Aggregate child records with multiple values
I need to find the max date of 2 field on Object__c
Structure
Object_A is parent
Object__B is junction object where Object__A and Object__C are lookup fields
Object__c is object that has the date ...
- 393
0
votes
2
answers
50
views
how to take single field value when use the ',' in field value in csv file
for(sObject sObj : scope) {
AggregateResult ar = (AggregateResult)sObj;
Integer IdCount = (Integer)ar.get('cnt');
String accName = (String)ar.get('accName');
...
0
votes
1
answer
58
views
In Start Method I use Aggregated Query (queries use GROUP By ),then how to check In Execute Method Limit&Number of records we can get using this Query
In Start Method I use Aggregated Query (queries use GROUP By ),then how to check In Execute Method the Aggregate Query how much record Fetch Limit & Number of records we can get using Aggregate ...
0
votes
2
answers
468
views
SOQL to get all records with in a given date range
Below is my sample data:
I am trying to form a query where I can get records within a date range.
Date Range is start of the week to end of the week. For example I want all records that falls between ...
- 4,671
0
votes
2
answers
185
views
SOQL Aggregate query for related opportunity
I want to use accounts and related Opportunities in my LWC.
@AuraEnabled(Cacheable=true)
public static Account[] getAllAccounts(){
return [SELECT
Id,
...
- 29
0
votes
1
answer
72
views
Aggregate result not working in soql query
im new to visualforce and i have this problem:
I have a sObject with customField1__c, customField2__c, customField3__c.
In my main page (with a standard controller and an extension) i want to display ...
- 19
1
vote
1
answer
78
views
Combine values from two maps to a third map
The result should display a table of all accounts from a specific user and the aggregateresult of the related opportunities and sales.
I create a map (AccList) of all accounts and add the Ids to a ...
- 95
0
votes
1
answer
96
views
Bulkify Aggregation Trigger
Is there a way to combine the two aggregates below.
public class AccountClass {
public static void UpdatePartnerRollup(List<ID> AccID){
// Get Opp Account
Map<Id, Account&...
0
votes
1
answer
3k
views
APEX Aggregate Query - Query to get Sum of Amount of all opportunities of Account when stage is Closed Won
I am having trouble making the query to get Sum of Amount of all opportunities of Account when stage is Closed Won. Please help me out. This is the Query I wrote.
Select SUM(AMOUNT) , ID , Name from ...
0
votes
2
answers
1k
views
Null Pointer exception on a Map get()
I'm getting the following error because of this line of code (line 235) in my Before Update Trigger on Site Survey (when I hardcode the sum here, the test passes):
Integer sum = Integer.valueOf(aggSum....
- 467
0
votes
1
answer
188
views
Database.Stateful in Batch gives errors
I have tried an aggregate query, but the result size is too large. So I built a new batch to run over the data and create an aggregation over Account and Product. Condition is that the Revenue/...
0
votes
1
answer
227
views
SOQL aggregate functions number fields
Trying to use an aggregate query to count certain records. Now I know that Number type fields cannot be used in Group By so I'm trying the following:
SELECT Count(Id), Max(Number_Field__c) FROM ...
- 1,637
0
votes
1
answer
48
views
SOLVED: AggregrateResult is not returning Value
I am attempting to calculate the sum MRR of Assets related to an Account and add it to a field on the Opportunity object. The code doesn't show an error but does not return values when new ...
- 31
0
votes
1
answer
848
views
What is the maximum number of rows that can be counted by a SOQL count query?
I recollect that:
select count() from Contact
will now work for more than 50,000 rows (but can't find documentation on that improvement).
My question is whether or not there is a new (much higher) ...
- 133k
0
votes
1
answer
241
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 ...
- 291
6
votes
0
answers
311
views
SOQL Aggregate functions circumventing limits?
We're working in a managed package that leverages SOQL aggregate functions and we want to ensure we are considering governor limits appropriately. To prepare for it we have created a Scratch Org with ...
- 1,089
1
vote
2
answers
570
views
LWC for:each needs 3 levels, but SOQL query aggregate relationship limited to 2. What's your best practice?
so I have a structure like:
<template for:each={containers.data} for:item='container'>
<....> //container info
<template for:each={container.Pallets__r} for:item="pallet"&...
- 330
0
votes
1
answer
693
views
How to query the Campaign Member with the earliest FirstRespondedDate for each Account
I've been working away at this for a while now, and I'm starting to think SOQL just won't support it. This query works, but no matter how I order the grouping, I still get back more than one record ...
- 876
1
vote
1
answer
202
views
SOQL Aggregate query
I am trying to query the most recent ActivityDate on an Account. Since aggregate function does not work on the Standard ActivityDate field on Tasks, I created a custom field ActivityDate__c to return ...
- 33
1
vote
1
answer
1k
views
Counting RecordType records
I am using simple_salesforce in Python3 to send query_all() SOQL queries against the REST API.
Now:
sf.query_all('SELECT Id FROM RecordType') returns 53 records.
But:
sf.query_all('SELECT COUNT(Id) ...
3
votes
1
answer
975
views
System.LimitException: Too many query rows: 50001 - How to solve it with a batch?
I need to create a summaries record for each result generated by the following query, which returns around 12K.
The trouble is the aggregate result has to query 600K records, so I get 'System....
- 69
0
votes
2
answers
3k
views
Aggregate query with filter in batch
I would like to use aggregate query in batch job but I'm running into an error:
HAVING expression must be grouped or aggregated
Would also like to understand how can I use count from the query ...
- 1,137
0
votes
0
answers
27
views
Aggregate Cumulative
It is possible to cumulate the aggregate results?
My code below works fine so far, I get for each month a sum of BWS but now I need the cumulative value.
For example:
January BWS - 100
February ...
- 451
0
votes
0
answers
128
views
Put Aggregateresult in Chart
I want to count the number of consultingdays each month from the current year.
And I want add this values into a line chart. But it doesn't work.
If I run the visualforce page I get the error
field '...
- 451
1
vote
1
answer
268
views
SOQL Get child with most rows grouped by parent
Let's say i have the following structure
Object Car is a parent of many (object) Incidents that are also children of an Area
So the table of incidents looks like this
INCIDENTS
ID CAR AREA
...
- 11
0
votes
1
answer
469
views
Can you include only top 10 records in SOQL group?
I have the following Query
[Select Supplier__c, Avg(Total_Sales__c) from Submittal group by Supplier__c order by Purchase_Date__c DESC]
The issue is that I only want to include the latest 10 records ...
- 373
1
vote
1
answer
407
views
How to get all account IDs with only one opportunity which is in 'New Opportunity' stage using SOQL query?
I attempted using this query, however this gets all accounts that have one related opportunity in the 'New Opportunity' stage, but the account may have more related opportunities in any other stage. ...
- 440
1
vote
1
answer
1k
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 ...
- 415
0
votes
1
answer
1k
views
Updating parent record via Batch Apex
I am developing a batch Apex process to assign an 'engagement score' to Account based on event/ campaign attendance.
We use Campaigns to track events and event guests.
What I would like this batch ...
- 115
3
votes
1
answer
247
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 ...
- 63
0
votes
1
answer
238
views
Want to fetch field from AggregateQuery
I created a Custom Settings and add some records in it. On the basis of that value i write an aggregate query and i want to store that field value in String.
// List to fetch all records from ...
- 35
0
votes
1
answer
620
views
Is it possible to output an aggregate query to a Map<Id, List<Contact>>
Is it possible to output an aggregate query like this:
Map<Id, List<Contact>> accountContactsMap = [SELECT aggregate query ...];
Where the Map key is the AccountId and the Map value is a ...
- 8,778
-2
votes
1
answer
1k
views
Write an aggregate query to return the sum of Closed Won opportunities for the current year (Close Date = THIS YEAR) grouped by the account [closed]
AggregateResult[] groupedResults
= [SELECT AVG(Amount)aver FROM Opportunity];
Object avgAmount = groupedResults[0].get('aver');
- 1
3
votes
1
answer
234
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()?
...
- 2,182
0
votes
1
answer
72
views
Aggregate result
The aggregate for the field amount and JZ doesnt work.
CLASS
Public Class VTP2_class {
Public Summary[] Summaries {get; set;}
Public VTP2_class() {
AggregateResult[] results = [SELECT COUNT(Id) ...
- 451
0
votes
1
answer
1k
views
Count the number of details in master-detail relationship
How can I count the number of details in master-detail relationship?
Let us suppose I have
Detail_Type__c and Master_Type__c.
Now if I have and Id of a Master_Type__c record I can do the following ...
- 379
6
votes
1
answer
624
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
...
- 8,778
3
votes
0
answers
466
views
Is applying a LIMIT to a Count() query an optimization?
Salesforce has recently updated the way that the Apex Governor counts Count() query row limits so that the entire aggregate is a single SOQL Row (as opposed to previously where each aggregated row was ...
- 133
0
votes
2
answers
188
views
Combining SOQL Queries
I need to pull out a query that fetches:
From My Team object and Package object. Package is a child of Teams
From teams I need to pull how many packages is currently in teams and how much each ...
- 53
2
votes
1
answer
83
views
Finding MIN(1) date in a data extension - error message about aggregate functions
I'm currently trying to find a list of email Addresses with a particular date in a data extension. The data extension can contain multiple instances of the same email address, but with different dates ...
- 822
0
votes
1
answer
102
views
SQOL - MAX condition in left inner join
I am getting a syntax error but can't quite figure out what I have mistyped. Can anyone spot what I am missing?
Select ID,Name FROM Employees__c WHERE Account__r.Tag__c INCLUDES ('Inactive') AND ID ...
- 1,453
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
0
votes
1
answer
144
views
Errors with AggregateResult
I am getting an error on line 20 invalid character in identifier: Inspection__Service__c.
public with sharing class AddedServicesGrouping {
public Inspection_Service__c[] Summaries {get; set;}
...
- 81
0
votes
2
answers
50
views
Manual Rollup (Sum) behaving differently than Reports and AggregateResult Sum
I'm not sure what is going on here. I'm wrote/summed up some anonymous apex to demonstrate some the discrepancies I'm seeing. Sometimes the values are accurate, sometimes close, sometimes just way off....
- 909
0
votes
1
answer
1k
views
Collect text fields in Aggregate Result Query
I have an aggregate result to process OpportunityLineItems by custom field PackageId - using min, max, avg etc for most fields works fine.
However, for a text field I want to collect all the values ...
- 1,290
0
votes
1
answer
2k
views
Get count of records from a child object which has a reference field to a parent object
To put this simply. I have 2 Objects
Let's call them SalesRep and Clients.
And the relation is a one-to-many relationship
(ie: SalesRep(Parent) -> Clients(Child))
So obviously in Clients Object, ...
- 23
1
vote
0
answers
136
views
Aggregate query on After Insert returns null
I am working on a class that will identify if an account has a primary contact when an contact is inserted or updated.trigger is on contact to fire on after update/After insert.
private void ...
- 157
1
vote
1
answer
37
views
Max aggrigate query has huge cost against # query rows returned limit
So I have a query:
AggregateResult[] results = [select max(Cash_Rewards_Number_Field__c) MaxName from Team__c];
This started causing 50001 too many rows returned errors in my trigger. When checking ...
- 118