How to check if value exists in each group (after group by)

Question

Assume I have a subscriptions table :

uid  | subscription_type 
------------------------  
Alex | type1
Alex | type2
Alex | type3
Alex | type4
Ben  | type2
Ben  | type3
Ben  | type4

And want to select only the users that have more than 2 subscriptions but never subscribed with type 1

The expected result is selecting "Ben" only.

I easy can found the users that have more than 2 subscribes using:

SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2

But how to check if in a group some value never exists?

Thanks for the help!

`But how to check if in a group some value never exists?` maybe you need to add a where clause with a `NOT EXITS(..)` ! — wildplasser, Nov 18 '15 at 16:07
I tried this: SELECT uid FROM subscribes GROUP BY cid,offer HAVING COUNT(*)> 2 AND NOT EXISTS ( SELECT uid, subscription_type FROM subscribes WHERE subscription_type = 'renew') But it's not working, returns empty table — Spivakos, Nov 18 '15 at 16:17
You need to *couple* the uid in the subquery to that of the main query. Then it should work. Also: it should be in a `WHERE` clause, not in a having clause. — wildplasser, Nov 18 '15 at 16:20

score 38 · Accepted Answer · answered Nov 18 '15 at 16:18

38

Try this query:

SELECT uid 
FROM subscribes 
GROUP BY uid 
HAVING COUNT(*) > 2
   AND max( CASE "subscription_type"  WHEN 'type1' THEN 1 ELSE 0 END ) = 0

answered Nov 18 '15 at 16:18

krokodilko

35,300
7
55
79

score 6 · Answer 2 · answered Nov 18 '15 at 16:54

To check if something doesn't exist, use NOT EXISTS(...):

SELECT uid
FROM subscribes su
WHERE NOT EXISTS (SELECT *
        FROM subscribes nx
        WHERE nx.uid = su.uid AND nx.subscription_type = 'type1'
        )
GROUP BY uid HAVING COUNT(*) > 2
        ;

A. Greensmith · Answer 3 · 2015-11-18T16:31:56.263

Create Sample Table:

CREATE TABLE subscribes
(
uid NVARCHAR(MAX),
subscription_type NVARCHAR(MAX)
)

Insert Values:

INSERT INTO subscribes
VALUES ('Alex', 'type1'), ('Alex', 'type2'), ('Alex', 'type3'), ('Alex', 'type4'),  ('Ben', 'type2'), ('Ben', 'type3'), ('Ben', 'type4')

SQL Query:

SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2
AND MAX(CASE subscription_type WHEN 'type1' THEN 1 ELSE 0 END) = 0

Output:

======
|uid |
------
|Ben |
======

How to check if value exists in each group (after group by)

3 Answers3

Linked