title: Full Outer Join, Coalesce, and Group By (Oh My!)
body: <p>I'm going to ask this in two parts, because my logic may be way off, and if so, the syntax doesn't really matter.</p>
  
  <ol>
  <li>I have 10 queries.  Each query returns month, supplier, and count(<em>some metric</em>).  The queries use various tables, joins, etc.  Not all month/supplier combinations exist in the output for each query.  I would like to combine these into a single data set that can be exported and pivoted on in Excel.</li>
  </ol>
  
  <p>I'd like the output to look like this:</p>
  
  <pre><code>Month | Supplier | Metric1 |Metric2 |..| Metric 10
  2018-01 | Supp1 | _value_ | _value_ |...| _value_ |
  2018-01 | Supp2 | NULL | _value_ |...| NULL
  </code></pre>
  
  <p>What is the best / easiest / most efficient way to accomplish this?</p>
  
  <ol start="2">
  <li>I've tried various methods to accomplish the above, but I can't seem to get the syntax quite right.  I wanted to make a very simple test case and build upon it, but I only have select privileges on the db, so I am unable to test it out.  I was able to create a query that at least doesn't result in any squiggly red error lines, but applying the same logic to the bigger problem doesn't work.</li>
  </ol>
  
  <p>This is what I've got:</p>
  
  <pre><code> create table test1(name varchar(20),credit int);
   insert into test1 (name, credit) values ('Ed',1),('Ann',1),('Jim',1),('Ed',1),('Ann',1);
   create table test2 (name varchar(10), debit int);
   insert into test2 (name, debit) values ('Ann',1),('Sue',1),('Sue',1),('Sue',1);
  
   select
      coalesce(a.name, b.name) as name,
      cred,
      deb
   from
      (select name, count(credit) as cred
          from test1 
          group by name) a
      full outer join 
      (select name, count(debit) as deb
          from test2
          group by name) b  on 
       a.name =b.name;
  </code></pre>
  
  <p>Am I headed down the right path?</p>
  
  <p>UPDATE:  Based on Gordon's input, I tried this on the first two queries:</p>
  
  <pre><code>select Month, Supp,
      sum(case when which = 1 then metric end) as Exceptions,
      sum(case when which = 2 then metric end) as BackOrders
  from (
          (
           select Month, Supp, metric, 1 as which
           from (
                  select (convert(char(4),E.PostDateTime,120)+'-'+convert(char(2),E.PostDateTime,101)) as Month, E.TradingPartner as Supp, count(distinct(E.excNum)) as metric
                  from db..TrexcMangr E
                  where (E.DSHERep in ('AVR','BTB') OR E.ReleasedBy in ('AVR','BTB')) AND year(E.PostDateTime) &gt;= '2018'
                ) a
           )
  
           union all
  
          (
           select Month, Supp, metric, 2 as which
           from (
                  select (convert(char(4),T.UpdatedDateTime,120)+'-'+convert(char(2),T.UpdatedDateTime,101)) as Month, P.Supplier as Supp, count(*) as metric
                  from db1..trordertext T
                      inner join mdid_Tran..trOrderPO P on P.PONum = T.RefNum
                  where T.TextType = 'BO' AND (T.CreatedBy in ('AVR','BTB') OR T.UpdatedBy in ('AVR','BTB')) AND year(UpdatedDateTime) &gt;=2018
                ) b
          )
       ) q
  group by Month, Supp
  </code></pre>
  
  <p>... but I'm getting a group by error.</p>