Gettings grouped sums from related tables into linq query columns

How can I rewrite this sql query as a linq query? Is it as easy as adding a group by statement...

It's even easier than that, because the SQL query in question uses single aggregate returning correlated subquery in the select clause, so the translation to LINQ is literally one to one - just use the corresponding C# operators and remember that in LINQ select goes last. And aggregate methods like Sum are outside the LINQ query syntax:

var query =
    from a in _context.Accounts
    where a.IsTrading
    select new
    {
        Total = (from ap in _context.AccountPositions
                 where ap.AccountId == a.AccountId && ap.IsOpen
                 select ap.Quantity * ap.LastPrice).Sum(),
        a.AccountId,
        a.Username,
        a.FirstName,
        a.LastName,
        a.AccountNumber
    };

But LINQ allows you to mix query syntax with method syntax, so the Sum part can be written more naturally using the later like this:

Total = _context.AccountPositions
    .Where(ap => ap.AccountId == a.AccountId && ap.IsOpen)
    .Sum(ap => ap.Quantity * ap.LastPrice),

Finally, if you are using Entity Framework, the relationship between the Account and AccountPosition will be expressed by something like

public ICollection<AccountPosition> AccountPositions { get; set; }

navigation property inside Account class. Which allows to forget about the join (correlation) conditions like ap.AccountId == a.AccountId - they will be applied automatically, and concentrate just on your query logic (see Don’t use Linq’s Join. Navigate!), e.g.

Total = a.AccountPositions.Where(ap => ap.IsOpen).Sum(ap => ap.Quantity * ap.LastPrice),

Inside LINQ query, the EF collection navigation property represents the so called grouped join - an unique LINQ feature (at least doesn't have direct equivalent in SQL). In LINQ query syntax it's expressed as join + into clauses. Let say you need more than one aggregate. Grouped join will help you to achieve that w/o repeating the correlation part.

So with your model, the query using grouped join will start like this:

from a in _context.Accounts
join ap in _context.AccountPositions on a.AccountId equals ap.AccountId into accountPositions

From here you have 2 variables - a representing an Account and accountPositions representing the set of the AccountPosition related to that Account.

But you are interested only in open positions. In order to avoid repeating that condition, you can use another LINQ query syntax construct - the let clause. So the query continues with both filters:

where a.IsTrading
let openPositions = accountPositions.Where(ap => ap.IsOpen)

Now you have all the information to produce the final result, for instance:

select new
{
    // Aggregates
    TotalPrice = openPositions.Sum(ap => ap.Quantity * ap.LastPrice),
    TotalQuantity = openPositions.Sum(ap => ap.Quantity),
    OpenPositions = openPositions.Count(),
    // ...
    // Account info
    a.AccountId,
    a.Username,
    a.FirstName,
    a.LastName,
    a.AccountNumber
};