CODEWITHSUNDEEP
sqlselectcount
Nizar
Nizar

Reputation: 85

sql select only if found more than once

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'

enter image description here

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.

Upvotes: 0

Views: 39

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

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

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

Upvotes: 1

D-Shih
D-Shih

Reputation: 46239

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

Upvotes: 1

Related Questions