SQL Decode Null Values

Question

I have the below query. When type_id is null I want it to be grouped by the name "unknown".
How can I do it.
I know there is a decode function but I'm not sure how to use it.

select type_id,
       name
  from test_table
 group by decode(type_id,'Unknown'),
          name;

how can I do it?

Answer 1

select decode(type_id, null, 'Unknown', type_id), name, count(*)
from
(
    select 'asdf' type_id, 'name1' name from dual union all
    select 'asdf' type_id, 'name2' name from dual union all
    select null type_id,   'name3' name from dual
) test_table
group by type_id,name;

I agree with @sql_mommy that CASE would probably look better. But I disagree about using TechOnTheNet as your primary source of information. You are usually better off with the official documentation, and the page for DECODE is a good example of why.

DECODE has some strange behavior: "In a DECODE function, Oracle considers two nulls to be equivalent." That behavior is not mentioned in the TechOnTheNet article.

Answer 2

For null, we have the NVL function. It can be used as follows

select nvl(type_id,'Unknown'),name from test_table group by
type_id,name;