title: Completing calculations in temp table and returning those results in a second query
body: <p>Fiddle - <a href="http://sqlfiddle.com/#!18/c2b80/17" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/c2b80/17</a></p>
  
  <p><strong>Tables:</strong></p>
  
  <pre><code>CREATE TABLE [OrderTable] 
  (
      [id] int,
      [OrderGroupID] int,
      [Total] int,
      [fkPerson] int,
      [fkitem] int
      PRIMARY KEY (id)
  ) 
  
  INSERT INTO [OrderTable] (id, OrderGroupID, Total ,[fkPerson], [fkItem]) 
  VALUES
    ('1', '1', '20', '1', '1'),
    ('2', '1', '45', '2', '2'),
    ('3', '2', '32', '1', '1'),
    ('4', '2', '30', '2', '2');
  
  CREATE TABLE [Person] 
  (
      [id] int,
      [Name] varchar(32)
      PRIMARY KEY (id)
  ) 
  
  INSERT INTO [Person] (id, Name) 
  VALUES ('1', 'Fred'),
         ('2', 'Sam');
  
  CREATE TABLE [Item] 
  (
      [id] int,
      [ItemNo] varchar(32),
      [Price] int
      PRIMARY KEY (id)
  ) 
  
  INSERT INTO [Item] (id, ItemNo, Price) 
  VALUES ('1', '453', '23'),
         ('2', '657', '34');
  </code></pre>
  
  <p>Original query:</p>
  
  <pre><code>WITH TABLE1 AS 
  (
      SELECT 
          -- P.ID AS [PersonID], 
          -- P.Name, 
          SUM(OT.[Total]) AS [Total], 
          i.[id] AS [ItemID],
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
          ot.fkperson
      FROM 
          OrderTable OT
      -- INNER JOIN Person P ON P.ID = OT.fkperson
      INNER JOIN 
          Item I ON I.[id] = OT.[fkItem]
      GROUP BY 
          -- P.ID, P.Name,
          i.id, ot.fkperson
  )
  SELECT 
      t1.fkperson,
      t1.[itemid],
      t1.[total],
      t1.[rownum]
      -- Totalrows = (SELECT MAX(rownum) FROM TABLE1)
  FROM 
      TABLE1 T1
  INNER JOIN 
      Person P ON P.ID = T1.fkperson
  </code></pre>
  
  <p>I have attempted to complete the sum function on a column in a temp table and join it back to the CTE. It either errors or I get the incorrect columns. The idea for this is to perform calculations in a temp table to improve performance of queries. How can I join a <code>sum()</code>'d column from a temp table to the original table and output the results? </p>
  
  <p>Current query:</p>
  
  <pre><code>CREATE TABLE #ot
  (
       fkperson int, 
       Total int
  )
  
  INSERT INTO #ot
      SELECT 
          fkperson, 
          SUM(total) AS [Total]
      FROM 
          OrderTable
      GROUP BY 
          [fkperson]
  
  WITH TABLE1 AS 
  (
      SELECT 
          ot.[Total], 
          i.[id] AS [ItemID],
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
          ot.fkperson
      FROM 
          #ot OT
      INNER JOIN 
          Item I ON I.[id] = OT.[fkItem]
      GROUP BY 
          i.id, ot.fkperson
  )
  SELECT 
      t1.fkperson,
      t1.[itemid],
      t1.[total],
      t1.[rownum],
      p.[Name],
      Totalrows = (SELECT MAX(rownum) FROM TABLE1),
      totalrows = @@ROWCOUNT
  FROM 
      TABLE1 T1
  INNER JOIN 
      Person P ON P.ID = T1.fkperson 
  </code></pre>