Populate records with default values if query doesn't result any results

Question

I have a query in Hive like below. This query works as expected for me.

select 
case 
when account_number = '4122268949' then 'Operating'
when account_number = '4127343176' then 'Reserve'
when account_number = '4125604850' then 'sFBo'
when account_number = '2651133000' then 'IDEAL'
when account_number = '4496851031' then 'PwB'
end as account_name,
account_number, 
count(distinct(file_name)) as file_name_count, 
count(*) as total_file_count, 
case when count(*) >=1 then 'Minimum_expected_files_processed' 
else 'Files_not_received_today' 
end as Files_validation
from 
database.table 
where 
account_number in (
'4122268949', 
'4127343176', 
'4125604850', 
'2651133000', 
'4496851031'
) 
group by 
account_number) tb1;

Question:

If in the table I don't have any records for 4496851031 and 4127343176 account_numbers.

Then I want the result to have

----------------------------------------------------------------------------------------------------
|account_name| account_number| file_name_count| total_file_count|                  Files_validation|
----------------------------------------------------------------------------------------------------
| Operating  |     4122268949|              2 |              20 | Minimum_expected_files_processed | 
| Reserve    |     4127343176|              0 |              0  |         Files_not_received_today |
| sFBo       |     4125604850|              3 |              22 | Minimum_expected_files_processed |
| IDEAL      |     2651133000|              1 |              1  | Minimum_expected_files_processed |
| PwB        |     4496851031|              0 |              0  |         Files_not_received_today |
----------------------------------------------------------------------------------------------------

Edit

when I filter the database.table to add extra where clause that returns only 3 account numbers, I am unable to achieve what I want

sample query

select accounts.name as account_name,
   accounts.number as account_number,
   count(distinct b.file_name) as file_name_count,
   count(b.file_name) as total_file_count,
   case when count(b.file_name) >= 1 then 'Minimum_expected_files_processed' 
        else 'Files_not_received_today' 
        end as Files_validation
from accounts
left join db.table b on b.account_number = accounts.number
where b.load_date >= '2019-08-20'
group by accounts.number, accounts.name

Answer 1

You should create a table with account numbers and names (let's call it accounts). Then you can LEFT JOIN that to your other table to get the results you want:

select accounts.name as account_name,
       accounts.number as account_number,
       count(distinct table.file_name) as file_name_count,
       count(table.file_name) as total_file_count,
       case when count(table.file_name) >= 1 then 'Minimum_expected_files_processed' 
            else 'Files_not_received_today' 
            end as Files_validation
from accounts
left join `table` on table.account_number = accounts.number
group by accounts.number, accounts.name

If you really don't want to create a table for accounts, you can rewrite the query using a derived table for the account data:

select accounts.name as account_name,
       accounts.number as account_number,
       count(distinct table.file_name) as file_name_count,
       count(table.file_name) as total_file_count,
       case when count(table.file_name) >= 1 then 'Minimum_expected_files_processed' 
            else 'Files_not_received_today' 
            end as Files_validation
from (select '4122268949' as number, 'Operating' as name
      union
      select '4127343176' as number, 'Reserve' as name
      union
      select '4125604850' as number, 'sFBo' as name
      union
      select '2651133000' as number, 'IDEAL' as name
      union
      select '4496851031' as number, 'PwB' as name) accounts
left join `table` on table.account_number = accounts.number
group by accounts.number, accounts.name