I’m a newbie in HIVE. I want to include 0 rows in results
I have one table like
- - - - - - - - - - - - - - - data_source_id, part_hour, ... - - - - - - - - - - - - - - -
this is my query
SELECT data_source_id, COUNT(*) as count FROM data_source WHERE part_hour = 2022041618 GROUP BY data_source_id;
example result is
data_source_id, count 12 , 35 13 , 36
but my desired result is
data_source_id, count 12 , 35 13 , 36 15. , 0
How can I get a 0 in results?
Advertisement
Answer
You can do this but you need to remove where clause.
SELECT data_source_id, SUM(case when part_hour = 2022041618 then 1 else 0 end ) as count FROM data_source GROUP BY data_source_id;
You can also do using self join.
EDIT – I changed below SQL as per your need. If you have partitions on part hour and data_source_id, your SQL should be quick.
select distinct d.data_source_id, nvl(rs.count,0) FROM (select distinct data_source_id from data_source) d left join (SELECT data_source_id, COUNT(*) as count FROM data_source WHERE part_hour = 2022041618 GROUP BY data_source_id) rs on rs.data_source_id=d.data_source_id;