Question: Using MSSQL

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:

```
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

```

This yields a result like:

```
755   2
1259  2
2031  1
3888  0

```

Meaning that all but 3888 have at least one paid account.

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). 

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.

Any ideas?

Answer: 
Re-using the query from the question you can create a CTE (or a subquery) and aggregate the results:

```
;WITH CTE_UserAccounts AS (
    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
)
SELECT
    COUNT(CASE WHEN highest > 0 THEN 1 END) AS [Paid],
    COUNT(CASE WHEN highest = 0 THEN 1 END) AS [Free]
FROM 
    CTE_UserAccounts;

```
```
SELECT 
COUNT(DISTINCT CASE WHEN t2.free_paid = 'P' THEN t1.user_id END) as atleast_one_paid,
COUNT(DISTINCT CASE WHEN t2.free_paid <> 'P' THEN t1.user_id END) as onlyfree
FROM users t1 
INNER JOIN accounts t2 ON t1.user_id = t2.user_id
WHERE t2.account = 'A'

```