SOQL query aggregation

Question

I need to provide statistics to the users in my VF Page, showing the total of records they own where the status is New, In progress, Approved, Pending, Converted

I have tried the below query in my custom controller but I don't know how to combine different status's and I am getting an error

Save error: Grouped field should not be aggregated: Status__c*

Request_for_System_Change__c is the name of the custom object

public list<Request_for_System_Change__c> getStats(){

RETURN  [
        SELECT 
            Count(Status__c)
            , MAX(Request_Title__c)
        FROM 
            Request_for_System_Change__c
        WHERE 
            ownerid=:userInfo.getUserID()
        GROUP BY 
            Status__c
        HAVING 
            Status__c='New'         
        ORDER by 
            MAX(LastModifiedDate) DESC
            ];  
} 

Also, what is the best way to return this information - is a <L:ist> the way to go?

Answer 1

The code you have posted should return a List<AggregateResult>, so it is unclear how you got it to compile. It seems you want something like the following:

public static Map<String, Integer> getMyRecordCounts()
{
    Map<String, Integer> recordCounts = new Map<String, Integer>();
    for (AggregateResult aggregate : [
        SELECT Status, count(Id) records FROM Request_for_System_Change__c
        WHERE OwnerId = :UserInfo.getUserId()
        GROUP BY Status
    ])
        recordCounts.put(
            (String)aggregate.get('Status'),
            (Integer)aggregate.get('records')
        );
    return recordCounts;
}