function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sfdcFanBoysfdcFanBoy 

Aggregate Results Order By

Hello All,

 

I have the below aggregate Results query which is working fine.

 

public List <AccountPDF> getAccountsSnapshotYearly(){

    AggregateResult[] groupedResults = [SELECT Customer_Name__c,MAX(Segment__c),SUM(Load_Count__c) FROM Snapshot__c GROUP BY Customer_Name__c ORDER BY Customer_Name__c ASC];
       
        for (AggregateResult ar : groupedResults)  {
                                   
            snapshotResult.add(new AccountPDF(String.valueOf(ar.get('Customer_Name__c')), String.valueOf(ar.get('expr0')), String.valueOf(ar.get('expr1'))));
        } 
     return snapshotResult;
        
    }

 

The result of the above code is something like this.  Grouped and ordered by Customer Name.

 

Customer Name      Max(segment)             sum(load count)

ABC                                          7                                    40

XYZ                                           3                                    10

CAB                                          2                                    30

QWE                                        9                                    20

 

Now I want to order/sort this table by Sum(load count) - Should display in this order 10, 20, 30, 40 of load count. not in customer name order.

 

But when I include that 'ORDER BY Load_Count__c' in the aggregate result query, it throws error - Ordered field must be grouped or aggregated: Load_Count__c.  The Load_Count__c field is already aggregated in the query but still it shows the same error. 

 

Cant we order by an aggregated field?

 

Any other alternatives available?

Best Answer chosen by Admin (Salesforce Developers) 
nj07nj07

Hi Manish,

 

Use the Below Query: 

 

AggregateResult[] groupedResults = [SELECT Customer_Name__c,MAX(Segment__c),SUM(Load_Count__c) FROM Snapshot__c GROUP BY Customer_Name__c ORDER BY SUM(Load_Count__c) ASC];

 

Note: You have to use Grouped or aggregate field for order by clause

 

 

If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.

 

Thanks,

Nilesh Jain

All Answers

nj07nj07

Hi Manish,

 

Use the Below Query: 

 

AggregateResult[] groupedResults = [SELECT Customer_Name__c,MAX(Segment__c),SUM(Load_Count__c) FROM Snapshot__c GROUP BY Customer_Name__c ORDER BY SUM(Load_Count__c) ASC];

 

Note: You have to use Grouped or aggregate field for order by clause

 

 

If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.

 

Thanks,

Nilesh Jain

This was selected as the best answer
sfdcFanBoysfdcFanBoy
Wonderful. Didn't think that way. All good!

Thanks a lot !!
Mike Dwyer 9Mike Dwyer 9
I found this post looking for an answer to the question, how to order the results of an aggregate query. There is something I don't understand here: The original poster shows results with Customer Names "ABC", "XYZ", "CAB", "QWE", and says "Grouped and ordered by Customer Name." In my eye, those results may be group by Customer Name but they are definitely NOT ORDERED BY Customer Name! 

My question is not can we order by aggregated fields, Can we order by GROUPED fields? Sorting the source records may aid the grouping process but it should not be necessary to SOQL. The expectation of an ORDER BY clause is to sort the OUTPUT. In SQL, that is exactly what it does, even in a grouped/aggregated query.

How do you get SOQL to sort the results of an aggregate query?
Mike Dwyer 9Mike Dwyer 9
Let me take this a step further. My query results do not look sorted. However, I am grouping on a picklist field. Apparently the order of values in the picklist is the order used in the ORDER BY clause. The picklists now have both a Value and an API Name. Do either of these drive the ORDER BY sequencing, or is it an internal sequence?