3

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?

5 Answers 5

2

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];
1
  • Yes @Jaime, that's correct. And I think you're right. No way to do it with pure SOQL from what I can tell. I'll mark your answer as the correct one, but you need to update your suggestion. I'm trying to get maxVal per category. So your maxVal variable can't be one value. What I ended up doing is just ordering by the sum and keeping track of which categories i've already added to a map.
    – fehays
    Mar 5, 2015 at 23:22
1

To filter in aggregate queries you typically use the HAVING clause http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_having.htm - e. g.

SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource
HAVING COUNT(Name) > 100

Now your filter is dynamic since you don't know it in advance.

You could either query twice or try it with a subquery.

4
  • Right, but I can't use having for the exact reason you mentioned. The value is not a constant. if I could do having sum(Number__c) = max(Number__c) that might work, but can't do it. The having clause cannot have a sub-query, so that won't work. What do you mean by query twice?
    – fehays
    Mar 5, 2015 at 1:03
  • First you query for max() in a separate soql and store result in a variable. Then query a second time with HAVING.
    – Uwe Heim
    Mar 5, 2015 at 1:07
  • But max() would not give me the max sum. I updated the sample data in my question to help demonstrate why that won't work. Max() for category1 will return 10 for user1 and 5 for user2. What I would need is 16 for user1 and not return user2 at all. Also, I know I can do it in apex. Just want to see if there's a SOQL way.
    – fehays
    Mar 5, 2015 at 1:12
  • ORDER BY max(...) LIMIT 1 would be my suggestion - have to pass on doing it in a single query, sorry. Group filtering finally will work only with HAVING
    – Uwe Heim
    Mar 5, 2015 at 1:22
0

Can you try using Something like this

SELECT Type,COUNT(id) accts FROM Account GROUP BY Type ORDER BY count(id) desc limit 2

3
  • That doesn't look relevant. That would return me the count of records, but I don't see how this helps get the max sum.
    – fehays
    Mar 5, 2015 at 4:21
  • This is just a template. what i am saying is count is also an aggregate function just like SUM. However the difference here is rather than doing MAX i am using order by and i am getting the top 2 types where record count is highest. So in your case as well you can get the SUM first and then order it in desc order and get the top 2, Makes sense ?
    – Kaw Sumit
    Mar 5, 2015 at 6:40
  • I see what you're trying to do, but it won't work in this case. Count is not the aggregate that I want to sort by. The sum is heavily dependent on the Number field. Not the number of rows. I could have 1 row for one Type that has a number value of 100 and 2 rows for Type2 that each have a number value of 10. I would want row 1 to display first if we are sorting. I can order by sum, but then i'll need to use apex to filter the rows i don't want.
    – fehays
    Mar 5, 2015 at 15:24
0

As i see the requirements you want the aggregate to return the two largest results of the aggregate function. then you can append your query with a where= MAX( Sum)LIMIT 2 according to the docs at :

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

you can also sort with soql using SORT BY Sum

0

I think you guys are getting hung up on the "limit 2". He only mentioned the top 2 rows because in the example he gave the max sum() value of 10 happens to show up for 2 different rows. It's possible that there wouldn't be a tie in which case he would only want the top 1, or conversely there could be many more rows that happen to share the same aggregate sum() value in which case he would want more than 2 rows returned.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .