You do a sub-select, e.g:
Select Amount,CloseDate,Name, (Select PricebookEntry.Product2Id, TotalPrice, UnitPrice, ListPrice From OpportunityLineItems) From Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST
Opportunity Products (OpportunityLineItem) is a Detail to the Master Opportunity, i.e. 1:n between Opportunity and Product lines. Products belong to a Pricebook, thus you have to traverse the PricebookEntry relationship to retrieve the Product ID (if you need it).
You can also query having OpportunityLineItem as the Primary object and referencing Opportuinty via the Opportunity relationship:
Select Select PricebookEntry.Product2Id, TotalPrice, Opportunity.Amount, Opportunity.CloseDate, Opportunity.Name From OpportunityLineItem WHERE Opportunity.StageName = 'Closed' ORDER BY Opportunity.Amount
You can't go directly from Product2 to Opportunity because there is no direct relationship between the two, i.e. the relationship is via PricebookEntry.
A useful tool in discovering relationships between objects is to use the Force.com Eclipse plugin. You can double click on the salesforce.schema item and it will open a GUI for building queries:
Since you are trying to update a parent record based on the entire group of related child records it seems to me you will need to use a query inside of an apex class or trigger.
The way I would likely go about solving this would be to put a trigger on the opportunity object. Any time an opportunity is inserted or updated with a different status you will need to add the parent account ID to a set. After all your trigger logic fires, you will need to query all opps related to any parent account in your set and loop through all of the opp statuses like Maneesh said.
There may be some way to muscle in a solution using workflows or rollups, but anytime you are trying to do logic with a group of child records I have found that code and a query is required.
Best Answer
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.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)