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
reidar_mstrreidar_mstr 

Group by aggregate column

Hi guys,

 

I'm trying to write a query selecting top 5 sales persons, this doesn't work:

 

SELECT SUM(Amount)AmountSum,OwnerId FROM Opportunity GROUP BY OwnerId ORDER BY AmountSum DESC LIMIT 5

 

What is the way to sort by aggregate column? I've been searching for almost an hour now but I'm nowhere closer. 

Best Answer chosen by Admin (Salesforce Developers) 
clouduserclouduser

try this...

SELECT SUM(Amount) AmountSum,OwnerId FROM Opportunity GROUP BY OwnerId ORDER BY Sum(Amount) DESC LIMIT 5

All Answers

Geetha ReddyGeetha Reddy

Hi,

For Example,

U have 5 Sales Persons.

1.X

Amount : 1000

2.Y

Amount : 1500

3.Z

Amount : 2000

4.A

Amount : 2500

5.B

Amount : 3000

 

U want to display like this?
reidar_mstrreidar_mstr

Yes but with the descending order

 

So I want

X 50000

Y 45000

Z 39000
clouduserclouduser

try this...

SELECT SUM(Amount) AmountSum,OwnerId FROM Opportunity GROUP BY OwnerId ORDER BY Sum(Amount) DESC LIMIT 5

This was selected as the best answer
sekharasekhara

i = [Select id,name,amount from Objectname where cond desc amount];

 

Set<String> stateSet = new Set<String>();

for(objectname a : i)
stateSet.add(a.name); 


states = new String[stateSet.size()];
Integer i = 0;
for (String state : stateSet) { 
states[i] = state;
i++;

}
states.sort();

}

 

In Aggregate results ,

 

[Select name,sum(Amount) amt  from objname where cond ORDER BY Name]

 

try this if any issues let me know.