7 - SOQL : Aggregate functions

 SOQL : Aggregate functions

Example : SELECT StageName, COUNT(Id), MIN(Amount), MAX(Amount), AVG(Amount) FROM Opportunity GROUP BY StageName



Here's a tabular overview of SOQL aggregate functions:
Aggregate FunctionDescriptionExample Query
COUNT()Counts the total number of records.SELECT COUNT() FROM Account WHERE Industry = 'Technology'
COUNT(fieldName)Counts non-null values in a specified field.SELECT COUNT(Name) FROM Account WHERE Industry = 'Technology'
SUM(fieldName)Calculates the total sum of a numeric field.SELECT SUM(Amount) FROM Opportunity WHERE StageName = 'Closed Won'
AVG(fieldName)Calculates the average of a numeric field.SELECT AVG(Amount) FROM Opportunity WHERE StageName = 'Closed Won'
MIN(fieldName)Returns the minimum value of a specified field.SELECT MIN(CloseDate) FROM Opportunity WHERE StageName = 'Closed Won'
MAX(fieldName)Returns the maximum value of a specified field.SELECT MAX(CloseDate) FROM Opportunity WHERE StageName = 'Closed Won'
GROUP BYGroups records based on the specified field.SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
GROUP BY ROLLUPGroups records with subtotals and grand totals for each group.SELECT Industry, COUNT(Id) FROM Account GROUP BY ROLLUP(Industry)
HAVINGFilters grouped records based on aggregate values, used with GROUP BY.SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry HAVING COUNT(Id) > 10
These functions allow for efficient and simplified data analysis directly within SOQL

                                        
                    


Using aggregate Functions in Apex Code

// Without Aliases / Using Expressions
AggregateResult[] groupedResult = [SELECT StageName, MAX(Amount), MIN(Amount), AVG(Amount) FROM Opportunity GROUP BY StageName];

for(AggregateResult result: groupedResult){
    System.debug('StageName: '+result.get('StageName')+' Max Amount: '+result.get('expr0')+' Min Amount: '+result.get('expr1')+' Avg Amount: '+result.get('expr2'));
}


// Using Aliases
AggregateResult[] groupedResult = [SELECT StageName, MAX(Amount) maxAmount, MIN(Amount) minAmount, AVG(Amount) avgAmount FROM Opportunity GROUP BY StageName];

for(AggregateResult result: groupedResult){
    System.debug('StageName: '+result.get('StageName')+' Max Amount: '+result.get('maxAmount')+' Min Amount: '+result.get('minAmount')+' Avg Amount: '+result.get('avgAmount'));
}

    

Comments

Post a Comment

Popular posts from this blog

API Names in Salesforce

 In Salesforce, an API Name is a unique identifier for metadata components like fields, objects, or custom settings that enables external systems, scripts, and integrations to reference Salesforce data programmatically. The API name remains consistent, even if the label displayed in the user interface changes, making it essential for maintaining integration stability. Key Characteristics of API Names Field and Object API Names : Standard object and field API names generally mirror their label names, but with spaces removed. Custom fields and objects have the suffix __c , denoting them as custom. Example: A custom object called "Project" would have an API name of Project__c . A custom field called "Start Date" on the Project object would have an API name of Start_Date__c . Case-Sensitivity : API names in Salesforce are case-sensitive , so it’s essential to match them exactly when using them in code or integrations. Usage : API names are used in SOQL queries, Apex co...
Read more

18 - LWC - BEST PRACTICES - For accessing HTML elements and their values in JavaScript

 In Lightning Web Components (LWC) , best practices for accessing HTML elements and their values in JavaScript involve using template.querySelector , template.querySelectorAll , and data-* attributes. Here’s a guide to help you follow these best practices effectively: 1. Use template.querySelector and template.querySelectorAll Since IDs can be unpredictable in LWC , the template.querySelector method is generally preferred. You can use CSS selectors , including class names, tag names, and data-* attributes, to locate elements. html Copy code <!-- template.html --> < lightning-input class = "my-input" data-id = "specialInput" value = "Sample Value" > </ lightning-input > javascript Copy code // component.js // Accessing the element const inputElement = this . template . querySelector ( '.my-input' ); // Getting the value const inputValue = inputElement. value ; 2. Use data-* Attributes for Specific Identifiers For ele...
Read more

6 - Object Relationships (Lookup and Master-Detail and Junction Objects)

 Here's a tabular comparison of the Lookup and Master-Detail relationship types in Salesforce: Feature Lookup Relationship Master-Detail Relationship Definition and Usage Loosely coupled; records are linked but can exist independently Tightly coupled; child record depends on the parent for existence Data Integrity and Ownership Child record does not inherit parent’s sharing or ownership Child inherits sharing, permissions, and ownership from the parent Roll-up Summary Fields Not available Available; aggregates values from child to parent Required Field Optional; child can exist without a parent Required; child must have a parent Cascade Delete Optional (not automatic) Automatic; deleting parent deletes all related child records Security and Access Child has independent sharing settings Child inherits sharing settings from the parent Hierarchy Depth Flexible; supports multi-level relationships Limited to strict parent-child hierarchy with up to two relationships This structure pro...
Read more