Implement SQL query with conditional sum and how does it work?

admin December 18, 2021

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