Grouping of Null and Not Null fields

Asked By 110 points N/A Posted on -

Hi,

I have to form a query in such a way that all the NULL values in a column are grouped together and Not NULL values are grouped separately.

In algorithm form my output should be:
Total_Count            Columnxyz
number1                    NULL
number2                    All Not NULL

SHARE
Answered By 0 points N/A #89141

Grouping of Null and Not Null fields

You can use the following code to achieve the result:

Select count(1) as Total_Count,
NVL2 (column_name,
'null',
'All Not NULL'
) as Columnxyz
from table_name
group by
NVL2 (column_name,
'null',
'All Not NULL'
)
order by Columnxyz;

Answered By 110 points N/A #89143

Grouping of Null and Not Null fields

Thanks a lot. It did help me solve my problem. Can you explain a bit about NVL2 function? Its syntax and its use.

Answered By 0 points N/A #89144

Grouping of Null and Not Null fields

Hi,

The syntax of the function is :
NVL2( string1, value_if_NOT_null, value_if_null )
string1 – is the column name
value_if_NOT_null – the value returned if string1 is not null
value_if_null – the value returned if string1 is null

NVL2 is an extension of NVL function as it lets you substitute values for not only NULL column values but also gives you an opportunity to work with Not Null column values.

Answered By 0 points N/A #89145

Grouping of Null and Not Null fields

Also you can consider the following example for a better understanding :

Suppose there is a table employee with the below mentioned fields:
Employee_Id
Employee_Name
Dept_Id
Dept_Name
Dept_Desc1
Dept_Desc2

select Employee_Id,Employee_Name,Dept_Id,
NVL2(Dept_Name,Dept_Desc1,Dept_Desc2)
from employee;

The above query gives the requested column and substitutes Dept_Desc1 if Dept_Name is Not NULL else substitutes Dept_Desc2 if Dept_Name is NULL.

Answered By 110 points N/A #89146

Grouping of Null and Not Null fields

The example was of tremendous help. It helped me understand the function. Thanks so much for your input.