SOQL GROUP BY

SOQL Tutorial What is SOQL SOQL Real Life Scenario SOQL Naming Conventions SOQL WHERE SOQL Comparison Operators SOQL Logical Operators SOQL ORDER BY SOQL LIMIT SOQL OFFSET SOQL Aggregate Functions SOQL GROUP BY SOQL HAVING SOQL Relationship Queries SOQL Date Literals SOQL Date Functions SOQL History Objects SOQL Sharing Objects SOQL in APEX Apex Variables in SOQL Queries Static and Dynamic SOQL SOQL return types SOQL Governor Limits Efficient SOQL Queries

SOQL does support grouping by multiple fields. The grouping order is from left to right.

Example! Find the count of clinics in various states and cities.

SELECT Count(Id), State__c, City__c
FROM Clinic__c
GROUP BY State__c, City__c

This query will return a table of this type:

Count(ID)	State__c	City__c
40	Florida	Orlando
80	Florida	Miami
3	Illinois	Chicago
7	Illinois	Rockford
12	Nevada	Las Vegas
10	Nevada	Reno
6	Nevada	Henderson

Using Aliases in GROUP BY

In SQL, we can alias any column being fetched from the database table, but in SOQL, this is not allowed.

However, we can still alias fields fetched when the SOQL query contains a group by clause.

SELECT count(Id) No_Of_Clinics, State__c state
FROM Clinic__c
GROUP BY State__c

Note that alias notation does not use as like SQL does.

It is also important to note, that even if you don’t specify an alias, all aggregated fields automatically get an implied alias in the form expr(n) where n starts from 0, and from left to right.

For Instance, Query:

SELECT Count(Id), SUM(Booking_Amount__c), Clinic__c
FROM Appointment__c
GROUP BY Clinic__c

Here Count(Id) automatically becomes expr0 and SUM(Booking_Amount__c) becomes expr1

Then to access these fields, you have to use their respective aliases in Apex.