CODEWITHSUNDEEP
hivegroup-byhiveql
shashank
shashank

Reputation: 410

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

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.

Upvotes: 1

Views: 1839

Answers (3)

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 1

slaakso
slaakso

Reputation: 9050

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

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • 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

Upvotes: 0

Related Questions