5

So I've added a count field to the object and based on your advice I built this SOQL, how can I now order the result based on the SUM?

    List<AggregateResult> result = [SELECT Searched_Phrase__c, SUM(Count__c) sum
FROM Portfolio_Catalog_Searched_Term__c
GROUP BY Searched_Phrase__c
limit 5];

I'm looking for help on what is the right way to write this SOQL: I have an object that holds the phrases that were entered into a search box (search terms), each record represents one search that was made. I want to count how many searches were made for each phrase and order this in a descending order.

I want to use this to show on a VF page the top 5 searched.

What is the right SOQL statement for this?

3
  • No. Nobody here advised you to add a "count field", you don't need such field! Please read the answers again. And you still miss ORDER BY clause, whether it'll be my ORDER BY COUNT(Id) DESC or for your current code - ORDER BY SUM(Count__c) DESC.
    – eyescream
    Jan 20, 2013 at 2:08
  • I added the field to make my app logic more efficient , nothing to do with my original question. Now I cannot find how to order by the SUM...
    – Dedo
    Jan 20, 2013 at 7:42
  • 1
    As I said - "ORDER BY SUM(Count__c) DESC". Have you even tried it. SELECT Searched_Phrase__c, SUM(Count__c) sum FROM Portfolio_Catalog_Searched_Term__c GROUP BY Searched_Phrase__c ORDER BY SUM(Count__c) DESC LIMIT 5.
    – eyescream
    Jan 20, 2013 at 8:47

3 Answers 3

12

Something like this?

SELECT Name, COUNT(Id)
FROM Search_Term__c
GROUP BY Name
ORDER BY COUNT(Id) DESC
LIMIT 5

(assuming you're storing the searched phrases in Name field)

Here's whole SELECT reference: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select.htm


You'll get the results as List<AggregateResult> and then you might have to cast them to some helper wrapper class or split into separate list of strings and integers... Something like this

List<Wrapper> results = new List<Wrapper>();
for(AggregateResult ar: [SELECT Name, COUNT(Id) c
FROM Search_Term__c
GROUP BY Name
ORDER BY COUNT(Id) DESC
LIMIT 5]){
    results.add(new Wrapper(String.valueOf(ar.get('Name')), Integer.valueOf(ar.get('c'))));
}

public class Wrapper{
    public String name {get;private set;}
    public Integer value {get;private set;}
    public Wrapper(String n, Integer v){
        name = n;
        value = v;
}
0
0
List<AggregateResult> result = [  
  Select phrase__c, count(Id)  
  From PhraseObject__c  
  Group by phrase__c  
  Order by count(Id) desc  
  Limit 5  
];
0
    public SelectOption[] UPloadNames{  
    public get {
    SelectOption[] uploadNames;
    uploadNames= new SelectOption[]{};
    for (AggregateResult   ar: [SELECT UploadName__c Name, COUNT(Id) 
         FROM Location_Uploads__c  GROUP BY UploadName__c ]) {
         uploadNames.add(new SelectOption(String.valueOf(ar.get('Name')),String.valueOf(ar.get('Name'))));
    } 
       return uploadNames;
                 } 

       private set;
    }

You must log in to answer this question.

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