Filter rows in SQL by sub-query/ joins-CodePudding

Here is a table of user, product and month.

CREATE TABLE USERS (user VARCHAR(1),product VARCHAR(1),month INT);
INSERT INTO USERS (user,product,month) VALUES 
("A","X",9),("A","X",10),("A","X",10),("A","Y",11),
("B","Y",12),("B","X",10),("C","X",10),("D","Z",11),("D","X",9);

How do I count the number of users of a certain product only (possibly also in a specific month)?

CodePudding user response：

Join a derived table where you get the users only using product X by using aggregation and checking if the minimum product equals the maximum product. Then use count(DISTINCT ...).

SELECT count(DISTINCT u1.user)
       FROM USERS u1
            INNER JOIN (SELECT u2.user
                               FROM USERS u2
                               GROUP BY u2.user
                               HAVING min(u2.product) = max(u2.product)
                                      AND min(u2.product) = 'X') upx
                       ON upx.user = u1.user;