title: SQL Union with Join
body: <p>This is a continuation of my <a href="https://stackoverflow.com/questions/42280207/sql-partial-full-outer-join">previous post</a>. My two tables look like this:</p>
  
  <pre><code>Table1
  Name    Id      Amount 
  Name1   1       99
  Name1   1       30
  Name1   9       120.2
  Name2   21      348
  Name2   21      21
  Name3   41      99
  Name6   20      23
  
  Table2
  Name    Id      Return Amount 
  Name1   1       99
  Name1   1       30
  Name1   9       120.2
  Name2   21      348
  Name2   21      21
  Name3   41      99
  Name4   19      923.2
  Name5   23      12
  </code></pre>
  
  <p>I need a resultant table that looks like this:</p>
  
  <pre><code>Name    Id      Amounts    Returns
  Name1   1       2          2
  Name1   9       1          2
  Name2   21      2          1
  Name3   41      1          1
  Name4   1       0          1
  Name5   23      0          1
  Name6   20      1          0
  </code></pre>
  
  <p>I've tried something like this:</p>
  
  <pre><code>SELECT
      [Name],
      [Id],
      ISNULL(count([Amount]), 0) as 'Amounts'
  FROM table1 AS A
  GROUP BY [Name], [Id]
  UNION (
              SELECT
                  [Name],
                  [Id],
                  ISNULL(count([Return Amount]), 0) as 'Returns'
              FROM 
                  table2 AS B
              GROUP BY [Name], [Id]
  )
  </code></pre>
  
  <p>But that gives me:</p>
  
  <pre><code>Name    Id      Amounts
  Name1   1       4
  Name1   9       3
  Name2   21      3
  Name3   41      2
  Name4   1       1
  Name5   23      1
  Name6   20      1
  </code></pre>
  
  <p>I know I need to put the <code>Return Amount</code> in the SELECT statement but I'm not sure how to do that, everything I've tried results in a syntax error because of that union.</p>
  
  <p>What's the right way of going about this?</p>
  
  <p>Update 1:
  Tried</p>
  
  <pre><code>SELECT
      A.[Name],
      A.[Id],
      count(A.[Amount]),
      count(B.[Return])
  FROM (
      SELECT
          [Name],
          [Id],
          count([Amount]) as 'Amounts'
      FROM table1
      UNION
      SELECT 
              [Name],
              [Id],
              count([Return]) as 'Returns'
      FROM table2
  ) AS A
  LEFT JOIN table2 as B on A.[Id] = B.[Id]
  GROUP BY A.[Name], A.[Id]
  </code></pre>
  
  <p>But I get the error:</p>
  
  <pre><code>Msg 8120, Level 16, State 1, Line 8
  Column 'table1.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  Msg 207, Level 16, State 1, Line 4
  Invalid column name 'Amount'.
  </code></pre>