Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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?

share|improve this question
    
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 '13 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 '13 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 '13 at 8:47
add comment

2 Answers

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;
}
share|improve this answer
add comment
List<AggregateResult> result = [  
  Select phrase__c, count(Id)  
  From PhraseObject__c  
  Group by phrase__c  
  Order by count(Id) desc  
  Limit 5  
];
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.