title: How to calculate SUM balance of all accounts in T-SQL?
body: <p>I have this table and data</p>
  
  <pre><code>CREATE TABLE #transactions (
      [transactionId] [int] NOT NULL,
      [accountId] [int] NOT NULL,
      [dt] [datetime] NOT NULL,
      [balance] [smallmoney] NOT NULL,
   CONSTRAINT [PK_transactions_1] PRIMARY KEY CLUSTERED 
  (   [transactionId] ASC)
  ) 
  
  INSERT #transactions ([transactionId], [accountId], [dt], [balance]) VALUES 
  (1, 1, CAST(0x0000A13900107AC0 AS DateTime), 123.0000),
  (2, 1, CAST(0x0000A13900107AC0 AS DateTime), 192.0000),
  (3, 1, CAST(0x0000A13A00107AC0 AS DateTime), 178.0000),
  (4, 2, CAST(0x0000A13B00107AC0 AS DateTime), 78.0000),
  (5, 2, CAST(0x0000A13D011D1860 AS DateTime), 99.0000),
  (6, 2, CAST(0x0000A13F00000000 AS DateTime), 97.0000),
  (7, 1, CAST(0x0000A13D0141E640 AS DateTime), 201.0000),
  (8, 3, CAST(0x0000A1420094DD60 AS DateTime), 4000.0000),
  (9, 3, CAST(0x0000A14300956A00 AS DateTime), 4100.0000),
  (10, 3, CAST(0x0000A14700000000 AS DateTime), 4200.0000),
  (11, 2, CAST(0x0000A14B00B84BB0 AS DateTime), 110.0000)
  </code></pre>
  
  <p>I need two queries. </p>
  
  <ol>
  <li><p>For each transaction, I want to return in a query the most recent balance for each account, and an extra column with a SUM of each account balance at that point in time.</p></li>
  <li><p>Same as 1 but grouped by date without the time portion. So the latest account balance at the end of each day (where there is a transaction in any account) for each account, but SUMed together as in 1.</p></li>
  </ol>
  
  <p>Data above is sample data that I just made up, but my real table has hundreds of rows and ten accounts (which may increase soon). Each account has a unique <code>accountId</code>. Seems quite a tricky piece of SQL.</p>
  
  <p>EXAMPLE</p>
  
  <p>For 1. I need a result like this:</p>
  
  <pre><code>+---------------+-----------+-------------------------+---------+-------------+
  | transactionId | accountId |           dt            | balance | sumBalances |
  +---------------+-----------+-------------------------+---------+-------------+
  |             1 |         1 | 2013-01-01 01:00:00.000 |     123 |         123 |
  |             2 |         1 | 2013-01-01 01:00:00.000 |     192 |         192 |
  |             3 |         1 | 2013-01-02 01:00:00.000 |     178 |         178 |
  |             4 |         2 | 2013-01-03 01:00:00.000 |      78 |         256 |
  |             5 |         2 | 2013-01-05 17:18:00.000 |      99 |         277 |
  |             7 |         1 | 2013-01-05 19:32:00.000 |     201 |         300 |
  |             6 |         2 | 2013-01-07 00:00:00.000 |      97 |         298 |
  |             8 |         3 | 2013-01-10 09:02:00.000 |    4000 |        4298 |
  |             9 |         3 | 2013-01-11 09:04:00.000 |    4100 |        4398 |
  |            10 |         3 | 2013-01-15 00:00:00.000 |    4200 |        4498 |
  |            11 |         2 | 2013-01-19 11:11:00.000 |     110 |        4511 |
  +---------------+-----------+-------------------------+---------+-------------+
  </code></pre>
  
  <p>So, for <code>transactionId</code> 8, I take the latest <code>balance</code> for each account in turn and then sum them. <code>AccountID</code> 1: is 201, <code>AccountId</code> 2 is 97 and <code>AccountId</code> 3 is 4000. Therefore the result for <code>transactionId</code> 8 will be 201+97+4000 = 4298. When calculating the set must be ordered by <code>dt</code></p>
  
  <p>For 2. I need this</p>
  
  <pre><code>+------------+-------------+
  |    date    | sumBalances |
  +------------+-------------+
  | 01/01/2013 |         192 |
  | 02/01/2013 |         178 |
  | 03/01/2013 |         256 |
  | 05/01/2013 |         300 |
  | 07/01/2013 |         298 |
  | 10/01/2013 |        4298 |
  | 11/01/2013 |        4398 |
  | 15/01/2013 |        4498 |
  | 19/01/2013 |        4511 |
  +------------+-------------+
  </code></pre>
  
  <p>So on date 15/01/2013 the latest account balance for each account in turn (1,2,3) is 201,97,4200. So the result for that date would be 201+97+4200 = 4498</p>