0

I'm having difficulty wrapping my head around this query. I have a table with a bunch of user features, and I'm trying to get all the users that have no features turned on. I know I need to do something with COUNT and GROUP BY, but SQL is really not my strong suit, and this needs to be done sooner rather than later, otherwise I'd do more research on my own. This is the table structure:

| UserFeatureID | UserID | FeatureID | Active
-----------------------------------------------
| 1             | 1      | 1         | false
| 2             | 1      | 2         | true
| 3             | 2      | 1         | false
| 4             | 2      | 2         | false
| 5             | 3      | 1         | false
| 6             | 3      | 2         | false
| 7             | 4      | 1         | true
| 8             | 4      | 2         | true

So the list of user ID's it'd return is 2 & 3 since they are the only ones that have all features turned off. Any help is appreciated, thank you!

  • If Active is a bit column you shouldn't use strings to represent the values. :-) – Aaron Bertrand Apr 26 '18 at 21:39
3

You can just map false/true to 0/1 and sum those per user. Those that have a zero sum have all false.

select userid 
from T
group by userid
having sum(case when active then 1 else 0 end) = 0;

A bit shorter is to use count instead of sum. Nulls are not counted, so we only need to map true to a value, and count all values. I use 1 here but you can pick any value that you want:

select userid 
from T
group by userid
having count(case when active then 1 end) = 0;
  • I'm sorry, I can see how my table was confusing. The Active column is the boolean. I changed it so it says true or false instead of 0 or 1. I did try the query any way, but it gave me an error since the it is a bit type. – Vandel212 Apr 26 '18 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.