Get Account lifetime value (sum of opportunities) and details

Question

I'm trying to get a table made of rows with Account details and the Sum of the Amount of the Opportunities for that Account.

SELECT Sum(Opportunity.Amount), Opportunity.AccountId FROM Opportunity GROUP BY AccountId

With this I get a simple list with the value I want and the AccountId.

Now I'd also like the Account details like PersonName.

How can I do that?

I've tried this:

SELECT Sum(Opportunity.Amount), Opportunity.AccountId, Account.PersonName 
FROM Opportunity
GROUP BY Opportunity.AccountId
INNER JOIN Account WHERE Account.Id=AccountId

But I get Unknown error parsing query. Why?

Answer 1

I don't have Person Accounts enabled so you'll have to add your own fields but something like this should be good start.

SELECT AccountId, Account.Name, Account.Website, SUM(Amount)
FROM Opportunity
GROUP BY AccountId, Account.Name, Account.Website

Bit annoying that you have to list every field you want to see in GROUP clause too. If you think you need this data in more places than this code (reports? list views?) then perhaps it's better idea to make a new "rollup summary" field on Account that'd aggregate for you the SUM(Amount)