Counting users which doesnt make a certain event

Question

Hi from the following table

id     event
 1   unknown
 1   unknown
 1   unknown
 2   unknown
 2         X
 2         Y
 3   unknown
 3   unknown
 4         X
 5         Y

i want count all the amount of users which in all of their rows has unknown values

In this case they should be 2 ids out of 5

My attempt was :

select 
   count(distinct case when event != 'unknown' then id else null end) as loggeds,
   count(distinct case when event = 'unknown' then id else null end) as not_log_android,
   count(distinct event) as session_long
from table

but is completly wrong

Answer 1

With NOT EXISTS:

select t.id
from tablename as t
where not exists (
  select 1 from tablename where id = t.id and event <> 'unknown'
)
group by t.id

for the number of disinct ids:

select count(distinct t.id)
from tablename as t
where not exists (
  select 1 from tablename where id = t.id and event <> 'unknown'
)

See the demo

Answer 2

You can check this question: How to check if value exists in each group (after group by)

SELECT COUNT(DISTINCT t1.id)
FROM theTable t1
WHERE NOT EXISTS (SELECT 1 from theTable t2 where t1.id = t2.id and t2.value != 'unknown')

OR

SELECT COUNT(t.id)
FROM theTable t
GROUP BY t.id
HAVING MAX(CASE value WHEN 'unknown' THEN 0 ELSE 1 END) = 0

Answer 3

SELECT id
FROM YourTable
GROUP BY id
HAVING COUNT(*) = COUNT ( CASE WHEN event = 'unknown' THEN 1 END )

Answer 4

I would do aggregation :

SELECT id
FROM table t
GROUP BY id
HAVING MIN(event) = MAX(event) AND MIN(event) = 'unknown';