[SalesForce] How to use SOQL to aggregate values and only return the largest sum

I want to do something like this, but you can't nest these particular functions:

select 
  max(sum(Number__c)),
  User__r.Name,
  Category__c
group by
  User__r.Name,
  Category__c

If my result set is this:

Sum         User       Category
10          User1      Category1
6           User1      Category1
5           User2      Category1
10          User2      Category2
5           User1      Category2

I want to only return the 2 rows where the sum is 10 because they are the greatest in that category. Is there a way to do this in SOQL or am I limited to sorting in apex?

Best Answer

Fred are you looking for one pure SOQL statement with no Apex involved? It can't be done as far as I know.

With Apex you could do something like this.


-- First query to get the max
AggregateResult[] agResults = [
    SELECT User__r.Name, Category__c, SUM(Number__c) as thesum
    FROM SomeTable
    GROUP BY User__r.Name, Category__c
    ORDER BY SUM(Number__c) DESC
    LIMIT 1];

Integer maxVal = (Integer)agResults[0].get('thesum');

-- Second query to filter with the max value
agResults = [
    SELECT User__r.Name, Category__c, SUM(Number__c) as thesum
    FROM SomeTable
    GROUP BY User__r.Name, Category__c
    HAVING SUM(Number__c) = :maxVal];
Related Topic