Linq syntax to find subtotal of grouped rows in a DataTable and update the DataTable
<p>I have been scratching my head about this one... I have a table in memory, a DataTable structured like this:</p>

<pre><code>Input:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   09310   |    123    |    6.0    |    ?
-----------------------------------------------------------------
</code></pre>

<p>And I wish to use Linq to perform the following (I am pretty new to Linq!)</p>

<p>For each row that is for the same account, add together all the payments and write or update the subtotal field(s)
I do not want to collapse the table, the invoice numbers are going to be different.  My thought is that there are two ways that this can be done</p>

<p><strong>(A)</strong> start with a BLANK subtotal column for all records... the values from payment would be added together and then written into the subtotal column</p>

<p><strong>(B)</strong> when table created, I DUPLICATE the payment values into the subtotal fields.  Later, the linq would only have to add/replace the values in the same column</p>

<p>So we would ignore the ID and Invoice fields; its ACCOUNT and SUBTOTAL (and PAYMENT as well if using style (A))</p>

<pre><code>(A) Input: *(note that there are two records for 123)*
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    
-----------------------------------------------------------------
</code></pre>

<p> </p>

<pre><code>(B) Input:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    6.0
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    4.0
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    18.0
-----------------------------------------------------------------
</code></pre>

<p> </p>

<pre><code>Result:
   ID   |   Invoice | Account | Payment | Subtotal
-----------------------------------------------------------------
    0    |   03310   |    123    |    6.0    |    10.0
-----------------------------------------------------------------
    1    |   09728   |    123    |    4.0    |    10.0
-----------------------------------------------------------------
    2    |   07731   |    559    |    18.0   |    18.0
-----------------------------------------------------------------
</code></pre>

<p>And so, each Subtotal cell would have the total of all PAYMENTS for each unique ACCOUNT </p>

<p>I am thinking that style (B) would be easier because we only have to deal with those two columns</p>

<p>For style (B), I have tried something like</p>

<pre><code>rpTable.AsEnumerable().GroupBy(g =&gt; int.Parse(g.Field&lt;string&gt;("Account"))).Select(g =&gt; g.Sum(p =&gt; p.Field&lt;decimal&gt;("SubTotal")));
</code></pre>

<p>but I can tell its missing something.....hmmmm</p>