Get users that have at least one product code and also more than 10 of the other product code-CodePudding

The table that needs to be queried looks like this:

ID	UserID	ProductCodes
1	33	9999
2	456	3051
3	456	9999
4	456	3051
4	33	9999

How would I write a SQL query to find out which users have at least one productCodes = '9999' and also have more than 10 productCodes <> '9999'?

CodePudding user response：

You can use GROUP BY and HAVING:

SELECT
    UserID
FROM dbo.YourTable
GROUP BY 
    UserId
HAVING SUM(CASE WHEN ProductCodes = '9999' THEN 1 ELSE 0 END) >= 1
AND COUNT(DISTINCT ProductCodes) >= 11
;

CodePudding user response：

Use Case or Intersect (case is more performant)

SELECT UserID, SUM (case when ProductCodes='9999' then 1 else 0 end) PC9999
, SUM (case when ProductCodes<>'9999' then 1 else 0 end) PCNot9999
FROM dbo.Users
WHERE ProductCodes='9999'
GROUP BY UserID
HAVING SUM (case when ProductCodes='9999' then 1 else 0 end)>0 
AND SUM (case when ProductCodes<>'9999' then 1 else 0 end) >10