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
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;
This should solve your problem.
Thanks a lot. It did help me solve my problem. Can you explain a bit about NVL2 function? Its syntax and its use.
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.
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.
The example was of tremendous help. It helped me understand the function. Thanks so much for your input.
