title: SQL group by case statement with COUNT DISTINCT
body: <p>My table in SQL Server 2012 looks like this:</p>
  
  <pre><code>SaleDate    CustomerID    Customer    
  2014-03-01     NULL        George    
  2014-03-01     NULL        John        
  2014-03-01     125         Emy    
  2014-04-01     126         Natasha    
  2014-04-01     127         NULL   
  2014-05-01     128         Jack
  </code></pre>
  
  <p>I want to find out the number of unique customers per sale date, considering that:</p>
  
  <ul>
  <li>there are cases when the <em>CustomerID</em> is missing (but the <em>Customer</em> name is known)</li>
  <li>and there are cases when <em>CustomerID</em> exists (but the <em>Customer</em> name is missing)</li>
  </ul>
  
  <p>The desired output is:</p>
  
  <pre><code>SaleDate       UniqueCustomers
  2014-03-01          3
  2014-04-01          2
  2014-05-01          1
  </code></pre>
  
  <p>I tried this query, but - of course - I get the following error: <em>Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause</em>:</p>
  
  <pre><code>SELECT SaleDate,
         CASE
             WHEN Customer IS NULL THEN COUNT(DISTINCT CustomerID)
             ELSE COUNT(DISTINCT Customer)
         END AS "UniqueCustomers"
  FROM TableA
  GROUP BY SaleDate,
           CASE
               WHEN Customer IS NULL THEN COUNT(DISTINCT CustomerID)
               ELSE COUNT(DISTINCT Customer)
           END
  </code></pre>
  
  <p>There is not a possibility to UPDATE the table to get rid of the NULL values.</p>