Count a grouped by SOQL

Question

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?

Answer 1

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;
}

Answer 2

List<AggregateResult> result = [  
  Select phrase__c, count(Id)  
  From PhraseObject__c  
  Group by phrase__c  
  Order by count(Id) desc  
  Limit 5  
];

Answer 3

    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;
    }