title: How to group by two data fields in a common table expression
body: <p>I need to compare the final balances of two tables from different clients grouped by ID. The tables have the same ID's But one has multiple ID entries and the other does not. I need to sum row from the table with multiple entries so I have just one final number to do calculations with.</p>
  
  <p>I have the query that does this much, but now I need to get it to group by month as well. My current query totals it for all dates. The data actually needs to be sorted by ID and by month so they know the ending monthly balance for every ID. Is there a way to do this with what I have so far?</p>
  
  <p>Thanks in advance.</p>
  
  <p>This is what I have so far.</p>
  
  <pre><code>    -- Data setup
      CREATE TABLE [Table1]
      (
          [ID] INT,
          [cost] INT,
          [traceNumber] INT,
          [TheDate] DATE
      )
  
      INSERT [Table1]
      VALUES  (1, 200, 1001, '9/07/2011'),
              (1, -20, 1002, '9/08/2011'),
              (1, 130, 1003, '10/10/2011'),
              (2, 300, 1005, '10/10/2011')
  
      CREATE TABLE [Table2]
      (
          [ID] INT,
          [cost] INT
       )
  
       INSERT [Table2]
      VALUES  (1, 200),
              (2, 300)
  
      -- Query
      ;WITH [cteTable1Sum] AS
      (
          SELECT [ID], SUM([cost]) AS [cost]
          FROM [Table1]
          GROUP BY [ID]
      )       
      SELECT  [Table1].[ID], 
              [Table1].[TheDate], 
              [Table1].[traceNumber],
              [Table1].[cost] AS [Frost_Balance],
              cte.[cost] AS [SUM_Frost_Balance],
              [Table2].[cost] AS [Ternean_Balance],
              cte.[cost] - [Table2].[cost] AS [Ending_Balance]
      FROM [Table1]
      INNER JOIN [Table2]
          ON [Table1].[ID] = [Table2].[ID]
      INNER JOIN [cteTable1Sum] cte
           ON [Table1].[ID] = cte.[ID]
  </code></pre>
  
  <p>I tried this and  I get the wrong answer.</p>
  
  <pre><code>    WITH [cteTable1Sum] AS 
           ( SELECT [ID], SUM([cost]) 
           AS [cost] FROM [Table1] 
           GROUP BY [ID], [TheDate] )
  </code></pre>