Group by and check value for that group in hive/sql

Question

I have a table with below data.

id start     current
1  today     True
2  yesterday False
1  Monday    False
3  yesterday True
3  Monday    False
4  today     
4  Tuesday   
5  Wednesday True
6  Friday    
6  Monday    
7  Sunday    True
7  Tuesday   

I want to check how many ids contains all nulls in the current column and print that count.

I thought of using group by id and select ids where current is null but its not giving the appropriate count. I wan to count only if all the rows for particular id contains current as null.

Answer 1

Try this: http://sqlfiddle.com/#!9/31f6e/12

select count(distinct start)
from 
(
select start,max(case when current is not null then 1 else 0 end) mt
from data
group by start)a where mt=0

Answer 2

• First, find all the id(s) whose MAX(current) is NULL.
• Then, simply count them out.

Try the following query (will work in MySQL):

SELECT COUNT(DISTINCT IF(derived_t.max_current IS NULL, 
                         derived_t.id, 
                         NULL)) AS ids_with_all_null
(
SELECT id, MAX(current) as max_current 
FROM your_table 
GROUP BY id
) AS derived_t

Answer 3

You can use exists-clause for that. "Find count of individual id's which do not have rows that have value of current other than NULL"

select count(distinct d.id)
from data d
where not exists (
  select *
  from data d2
  where d2.id=d.id and d2.current is not null
)

See SQLFiddle