How to use SUM and GROUP BY in SOQL

Question

I have a problem when I try to get and information about Opportunities.

This is my Query:

List<Opportunity> opp = [SELECT o.id, o.ownerid, o.name, o.AccountId, o.Type, o.Amount, o.Account.Last_Meeting_Date__c from Opportunity o, o.Account a where ownerid =: UserInfo.getUserId()];

This query get all information about Opportunities related with the user... I need get a SUM(o.Amount) grouped by owner. Knowing this the new query AggregateResult is so:

[SELECT o.id id, o.ownerid owner, o.name name, o.AccountId account, o.Type type, sum(o.Amount) total, o.Account.Last_Meeting_Date__c last_meeting_date from Opportunity o, o.Account a where ownerid =: u.id group by o.id, o.ownerid, o.name, o.AccountId, o.Account.Last_Meeting_Date__c, o.Type]

How to know show this information into VisualForce Page. I need show the information in this format:

Opportunity Owner

Opportunity Name | Sum of Amount

Opportunity Owner

Opportunity Name | Sum of Amount

....

Thanks.

Answer 1

 //Use map to store the AggregateResult  and display  in VF page  
Map<String, Decimal> mapOwnerToAmount =  new Map<String, Decimal>();

for(AggregateResult  objAgr: [SELECT  SUM(Amount) total, owner.Name from Opportunity 
                         where ownerid =: UserInfo.getUserId() GROUP BY ownerId];)
{
    mapOwnerToAmount.put((String)objAgr.get('owner.Name'), (Decimal)objAgr.get('total'));
}

in VF page

<apex:repeat value="{!mapOwnerToAmount}" var="key">
Opp Owner Name: {!key}, Sum of Amount: {!mapOwnerToAmount[key]} </br>
</apex:repeat >