title: Aggregating SQL results, two tables, then summarizing results
body: <p>Using MSSQL</p>
  
  <p>I have a table of users, and a table of products to which they are subscribed.  Those subscriptions are either Free (F) or Paid (P).  I have joined the tables, converted the F/P value to numeric using a case statement, and then summed up those values by user ID, the idea being that anyone with only free accounts will have a sum of 0, those with at least one paid account a value 1 or greater. I've gotten this far with the following:</p>
  
  <pre><code>SELECT t1.user_id, SUM(
      CASE
          WHEN t2.free_paid = 'P'
              THEN 1
          ELSE 0
      END ) as highest
  FROM users t1 INNER JOIN accounts t2
      ON t1.user_id = t2.user_id
  WHERE t2.account = 'A'
  GROUP BY t1.user_id
  ORDER BY t1.user_id
  </code></pre>
  
  <p>This yields a result like:</p>
  
  <pre><code>755   2
  1259  2
  2031  1
  3888  0
  </code></pre>
  
  <p>Meaning that all but 3888 have at least one paid account.</p>
  
  <p>But now I would like to simply add those up somehow to get our two values, one a count of users with at least one paid account (3 in example), and a count of those with only free accounts (1 in example).  </p>
  
  <p>I tried declare two variables, e.g. @free and @paid and using a case statement to add to those values by wrapping that around the above and treating it as a subquery, but I am unable to get that run.</p>
  
  <p>Any ideas?</p>