Home » SQL & PL/SQL » SQL & PL/SQL » getting count of non-zero, non-null values per row (In a set of numeric fields, how can I get the number of fields with values >0)
getting count of non-zero, non-null values per row [message #273643] Thu, 11 October 2007 03:06 Go to next message
greatmacoy
Messages: 3
Registered: October 2007
Junior Member
Hi!

Imagine a table that has 5 numeric columns, in each row I need to determine the count of columns that has a value greater than zero and not null as well. In every row, it is possible that some of the columns are null or zero, in that case the count should be less than 5.

Is there a function in oracle that works exactly that way. The count function counts the number of rows. Is there a version of that function for counting non-zero/null numeric columns per row.

Thanks in advance.

Rodel
Re: getting count of non-zero, non-null values per row [message #273645 is a reply to message #273643] Thu, 11 October 2007 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Associate CASE with COUNT, ex. count(case when <OK> then 1 end).

Regards
Michel
Re: getting count of non-zero, non-null values per row [message #273662 is a reply to message #273645] Thu, 11 October 2007 04:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could use NVL2:
create table null_cols (col_1 number, col_2 number, col_3 number);

insert into null_cols values (null,null,null);
insert into null_cols values (null,null,1);
insert into null_cols values (1,1,null);
insert into null_cols values (1,1,1);

select nvl2(col_1,0,1) + nvl2(col_2,0,1) + nvl2(col_3,0,1) null_columns
      ,col_1
      ,col_2
      ,col_3
from   null_cols;

create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
NULL_COLUMNS           COL_1                  COL_2                  COL_3                  
---------------------- ---------------------- ---------------------- ---------------------- 
3                                                                                           
2                                                                    1                      
1                      1                      1                                             
0                      1                      1                      1                      

4 rows selected

Previous Topic: Can we use case stmt in update stmt
Next Topic: Count the characters
Goto Forum:
  


Current Time: Mon Dec 05 11:10:59 CST 2016

Total time taken to generate the page: 0.06406 seconds