sql select only if found more than once-CodePudding

I have a table with userid and feature. I want to select only those users with more than 1 feature selected. check out the table

    declare @tbl table (userid int, feature nvarchar(10))
    insert into @tbl(userid, feature)
    select 1, 'abc'
    insert into @tbl(userid, feature)
    select 1, 'xyz'
    insert into @tbl(userid, feature)
    select 2, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'abc'
    insert into @tbl(userid, feature)
    select 3, 'xyz'
    insert into @tbl(userid, feature)
    select 3, 'pqr'

My query should only return the count = 2 (userids 1 and 3 as they both have multiple features associated). In other words I want the count of userids with multiple features associated with them.

CodePudding user response：

You can try to use JOIN with condition aggregate function in HAVING

SELECT t1.userid
FROM @tbl t1
INNER JOIN @tbl t2 
ON t1.feature = t2.feature
AND t1.userid <> t2.userid
GROUP BY t1.userid
HAVING COUNT(DISTINCT t1.feature) > 1

CodePudding user response：

That's a simple aggregation: group by user and count.

select userid
from @tbl
group by userid
having count(*) > 1;