I am trying to write the SQL to generate the count of unique users who have purchased product B but have never purchased product C.
| user_id | product | date_purchased |
|---|---|---|
| 1 | A | 2015-01-10 00:00:00.000 |
| 1 | B | 2014-11-23 00:00:00.000 |
| 1 | C | 2015-05-01 00:00:00.000 |
| 2 | A | 2014-10-01 00:00:00.000 |
| 2 | C | 2014-12-23 00:00:00.000 |
| 3 | B | 2015-02-15 00:00:00.000 |
| 3 | D | 2014-09-23 00:00:00.000 |
| 3 | E | 2014-06-01 00:00:00.000 |
| 4 | E | 2014-12-14 00:00:00.000 |
| 4 | F | 2015-03-03 00:00:00.000 |
Someone suggested trying the following
SELECT COUNT(*) AS bought_b_no_c
FROM (
SELECT user_id
FROM table_purchases
WHERE product IN ('B', 'C')
GROUP BY user_id
HAVING SUM(product = 'C') = 0
) t;
However, SQL Server is saying I have an error as follows:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ‘)’.
Anybody know why this is happening and how to learn exactly how this work?
PS This was my original code
SELECT Count(*) AS bought_b_no_c
FROM (SELECT user_id,
Sum(bought_b_no_c) AS boolean_b_no_c
FROM (SELECT user_id,
product,
CASE
WHEN product = 'B' THEN 1
WHEN product = 'C' THEN -1
ELSE 0
END AS bought_b_no_c
FROM table_purchases) AS T
GROUP BY user_id) AS J
WHERE boolean_b_no_c = 1
