how to aggregate and return zero in non exixtent where condition-CodePudding

I have table like follows.

case_no    history_no
A22010021   1
A22010021   2
A22010021   3

select
    case 
        when max(history_no) is null 
            then 0 
        else max(history_no) end as max
from
    table
where
    case_no = 'A22010022' 
group by
    case_no

it returned

max
Null

when I set case_no = 'A22010021' it returned

max
3

My desired result is to get 1 where non-existent case_no is extracted.

max
0

What is the root cause of this? Are there any ways to achieve this?

Thanks

CodePudding user response：

I think group by clause is not required in your use case.

select coalesce(max(history_no),0)
   from table_case_no where case_no='A220100234'

if you want to use group by clause then use below query to fill missing case no to put 0 value.

select case_no,max(history_no) max_history_no
    from table_case_no  where  case_no = 'A22010022' group by case_no
union
--below query for non-existent case no
select 'A22010022' as id,0 
    where not exists
    (
        select 1 from table_case_no where case_no='A22010022'
    )
    ;