APEX Aggregate Query - Query to get Sum of Amount of all opportunities of Account when stage is Closed Won

Question

I am having trouble making the query to get Sum of Amount of all opportunities of Account when stage is Closed Won. Please help me out. This is the Query I wrote.

Select SUM(AMOUNT) , ID , Name from Opportunity Group by ID,Name

But this dosent give sum of All opps related to Account it gives individual Amount of opps.

Answer 1

It gives individual amount because you're grouping by Opportunity Id, so you are not grouping at all.
You should use just AccountId in both SELECT and GROUP BY clause.

SELECT AccountId, SUM(AMOUNT) total
FROM Opportunity
WHERE StageName = 'Closed Won'
GROUP BY AccountId

Please consider using a more selective WHERE clause: in a real life scenario you would have more than 50000 Opportunity, so you're going to hit the governor limit about SOQL Query Rows.
Documentation (emphasis mine)

Queries that include aggregate functions are still subject to the limit on total number of query rows. All aggregate functions other than COUNT() or COUNT(fieldname) include each row used by the aggregation as a query row for the purposes of limit tracking.