CODEWITHSUNDEEP
sql
Ravinesh
Ravinesh

Reputation: 13

SQL Group By Client Query

I have a table which contains client information. This table holds the License type information against each client.

enter image description here

I wish to query this table to only find records of clients who have Licence Type as 2 or 1 and nothing else. A client can have licence type 1, 2 and 3 such clients should not qualify in the result set.

Appreciate your any assistance on this.

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You can do this with group by and having:

select clientid
from t
group by clientid
having sum(case when licensetype not in (1, 2) then 1 else 0 end) = 0;

The having clause counts the number of unacceptable license types for each client. The = 0 says that there are none.

EDIT:

Perhaps this really does what you want:

having max(licensetype) = 2 and
       min(licensetype) >= 1  -- not clear if this is necessary

Upvotes: 1

Related Questions