Grouping of Null and Not Null fields

Asked By Stuart 110 points N/A Posted on - 04/28/2011

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

• Status: Open
• Question Views: 942
• Answer Count: 5
• Vote Up 11 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Brad03

Go To Solution

Best Answer

Best Answer

Answered By Brad03 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;

This should solve your problem.

About Brad03

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By Stuart 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.

About Stuart

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By Brad03 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.

About Brad03

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By Brad03 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.

About Brad03

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By Stuart 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.

About Stuart

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database