» » SOQL Group By or GROUP BY ROLLUP Clause - SOQL Basic | Salesforce Funda

SOQL Group By or GROUP BY ROLLUP Clause - SOQL Basic | Salesforce Funda

You can use the GROUP BY option in a SOQL query to avoid iterating through individual query results. That is, you specify a group of records instead of processing many individual records.

GROUP BY clause is used in SOQL query to group set of records by the values specified in the field. We can perform aggregate functions using GROUP BY clause.

Both GROUP BY and GROUP BY ROLLUP Clause are similar to each other but IN SOQL GROUP BY ROLLUP Clause returns multiple levels of subtotal rows. 

When we using GROUP BY ROLLUP Clause in a statement we can add up to three fields in a comma-separated list.


SOQL Syntax :-


//Group By Industry
Select Count(ID), Industry Name From Account Group By Industry
//Count multiple field with Name Alias 
Select Count(ID) IDCounts, Count(Name) NameCounts, Industry From Account Group By Industry

Group by with Having Clause :-

Group by with Having clause is used in SOQL query to apply condition's/filters based on group by clause values

SOQL Syntax :-


Select Count(ID), Industry Name From Account Group By Industry Having Industry IN ('Media', 'Energy')

GROUP BY ROLLUP Clause:-

Use the GROUP BY ROLLUP optional clause in a SOQL query to add subtotals for aggregated data in query results. This action enables the query to calculate subtotals so that you don’t have to maintain that logic in your code.It returns multiple levels of subtotal rows. When we using GROUP BY ROLLUP Clause in a statement we can add up to three fields in a comma-separated list.

SOQL Syntax :


//Query in Account records
SELECT Industry, Type, COUNT(Id) From Account GROUP BY ROLLUP (Industry, Type)
//Query in Lead records
SELECT Status, LeadSource, COUNT(Name) NameCount FROM Lead GROUP BY ROLLUP(Status, LeadSource)

GROUP BY CUBE Clause:-

Use the GROUP BY CUBE clause in a SOQL query to add subtotals for all combinations of a grouped field in the query results.

SOQL Syntax :-

SELECT Type, BillingCountry, GROUPING(Type) grpType, GROUPING(BillingCountry) grpCity, COUNT(Id) accnts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)
List of Aggregated functions in Group Function.

  • COUNT ( ) - COUNT ( ) method is used to find the total number of records in an object that matches the specified condition
  • COUNT ( FIELD_NAME ) - COUNT ( FIELD_NAME ) Method is used to the total number of records of a particular value in the specified value.
  • COUNT_DISTINCT ( ) - COUNT_DISTINCT() Method is used to find the total number of distinct non-null field values. This method ignored the null values and returns the non-null values.
  • SUM ( ) -  The SUM() method is used to return the Sum of all the value.
  • MIN ( ) - The MIN() method in SOQL is used to return the lowest values.
  • MAX ( )  - The MAX () method is used to return the maximum or the largest value.
SOQL Syntax :-

//This query returns the total number of Contact records in the database.
SELECT COUNT() FROM Contact
//This query returns the count of Contact records. In this example, 
//we are counting the number of Contact records using the Id field.
SELECT COUNT(Id) FROM Contact
//This query returns the count of distinct AccountId values in the Opportunity object. 
//It counts the number of unique AccountIds associated with the Opportunity records.
SELECT COUNT_DISTINCT(AccountId) FROM Opportunity
// This query calculates the sum of the Amount field from the Opportunity object. 
//It returns the total sum of all the Amount values.
SELECT SUM(Amount) FROM Opportunity
//This query retrieves the minimum CloseDate value from the Opportunity object. 
//It returns the earliest date among the CloseDate values.
SELECT MIN(CloseDate) FROM Opportunity
//This query retrieves the maximum Amount value from the Opportunity object.
//It returns the highest amount among all the Amount values.
SELECT MAX(Amount) FROM Opportunity
Feature :-

Labels: ,