Count (*) for row of a table 2

[TekMem]

I have created a view. Which looks like

Col1  col2  col3  col4      ....... Coln  
1       1           0               2/3*100

0       1     0     0               1/4*100

In coln I want to calculate percentage. I want to count number of ones in the row, then find out null value in the row and then get percentage. In other words I want to count null or one in the row.

Thanks.

 

[SantaMufasa]

TekMem,

Here is code that should work for you:

select * from tekmem;

      COL1       COL2       COL3       COL4       COLN
---------- ---------- ---------- ---------- ----------
         1          1                     0
         0          1          0          0

2 rows selected.

update tekmem 
   set coln = (nvl(col1,0)+nvl(col2,0)+nvl(col3,0)+nvl(col4,0))
               / length(col1||col2||col3||col4)*100;

2 rows updated.

select * from tekmem;

      COL1       COL2       COL3       COL4       COLN
---------- ---------- ---------- ---------- ----------
         1          1                     0 66.6666667
         0          1          0          0         25

2 rows selected.
***************************************************************

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TekMem]

Thankyou SantaMufasa. I could not follow what are we trying to do here
nvl(col1,0)+nvl(col2,0)+nvl(col3,0)+nvl(col4,0))
/ length(col1||col2||col3||col4)*100

 

[SantaMufasa]

I'm glad you asked, TekMem.

First of all, the correct behaviour of my code depends upon this fact: Each source column (col1 through col4) must contain either NULL, 0, or 1. Also, to avoid a "zero-divide" error, at least one of your columns must contain either a 1 or 0. I made these presumptions from your original description/specification. If my presumptions are incorrect, then please advise and I shall amend the code.

Next, "nvl(<expression-1>,<expression-2>)" means, "Evaluate <expression-1>; if it is NULL, then <expression-2> is the result. Otherwise, if <expression-1> is NOT NULL, then the result of the function is <expression-1>.

In your case, the effect of all of those "nvl" functions simply means, "add up all of the 1s that appear in your columns col1 through col4". ("0" values and NULL do not augment the result of the addition operations.)

Next, my use of "length(col1||col2||col3||col4)" means, "Concatenate ('||') all of your source columns. Since NULL takes up no space (i.e., has no length), evaluating the LENGTH of the concatenation of the columns discloses how many columns are NOT NULL.

Let me know if you need additional clarifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

select * from t1
/



C1 C2 C3 C4
---------- ---------- ---------- ----------
1 1 1
1 1 0

0 1 0 0
9 9 9 1
9 9 9 0
1 1 1 1
9 9 9 9
0

9 rows selected.


select c1,c2,c3,c4,
decode(count_non_nulls,0,0,count_ones/count_non_nulls)*100 pct
from
(
select c1,c2,c3,c4,
NVL((1-ABS(SIGN(c1-1))),0) +
NVL((1-ABS(SIGN(c2-1))),0) +
NVL((1-ABS(SIGN(c3-1))),0) +
NVL((1-ABS(SIGN(c4-1))),0) count_ones,
decode((ABS(SIGN(c1-1))),null,0,1) +
decode((ABS(SIGN(c2-1))),null,0,1) +
decode((ABS(SIGN(c3-1))),null,0,1) +
decode((ABS(SIGN(c4-1))),null,0,1) count_non_nulls
from t1
)
/

C1 C2 C3 C4 PCT
---------- ---------- ---------- ---------- ----------
1 1 1 100
1 1 0 66.6666667
0
0 1 0 0 25
9 9 9 1 25
9 9 9 0 0
1 1 1 1 100
9 9 9 9 0
0 0

9 rows selected.




In order to understand recursion, you must first understand recursion.

 

[TekMem]

You guys are amazing! Thanks to both of you and have a star as token of appreciation from me.