CODEWITHSUNDEEP
sqlplsqloracle11g
LearnsWhileDoing
LearnsWhileDoing

Reputation: 11

How do I count the occurrences of NOT NULL fields in a record

I am trying to count the number of times a record field has a value so that I can report that number later in the application.

I am finding several answers with various approaches using COUNT and GROUP BY but the results are all sums of the total occurrences for the entire table.

I am trying to limit the count to each record.

Table Example:
COL-1     COL-2     COL-3     COL-4   
VALUE               VALUE
          VALUE               VALUE
VALUE               VALUE     VALUE
VALUE

I need to count the fields of each record for the number of times a value appears.

Something similar to:

Result Concept:
COL-1     COL-2     COL-3     COL-4     Occurrences
VALUE               VALUE                   2
          VALUE               VALUE         2
VALUE               VALUE     VALUE         3
VALUE                                       1

Clarification: I do not actually need to list the columns and values in the result. I only need the accurate count for each record. I just wanted to illustrate the relationship between the "occurrences-value" and the record values in my question.

Thank you for all suggestions and input.

Upvotes: 1

Views: 175

Answers (3)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

You may use the dynamic SQL without listing all column names

DECLARE @sql VARCHAR(MAX)
DECLARE @tbl VARCHAR(100)

SET @tbl = 'sampletable' -- put your table name here

SET @sql = 'SELECT *, ' 

SELECT @sql = @sql + '(CASE WHEN ' + cols.name + ' IS NOT NULL THEN 1 ELSE 0 END) ' + '+'
  FROM sys.columns cols
 WHERE cols.object_id = object_id(@tbl); 

SET @sql = LEFT(@sql, LEN(@sql) - 1)
SET @sql = @sql + ' AS occurrences FROM ' + @tbl

EXEC(@sql)

Upvotes: 0

LauDec
LauDec

Reputation: 548

Or decode ;)

select t.*, DECODE(col1,null,0,1)+DECODE(col2,null,0,1)+
            DECODE(col3,null,0,1)+DECODE(col4,null,0,1) cnt
from my_table t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just use case:

select t.*,
       ( (case when col1 is not null then 1 else 0 end) +
         (case when col2 is not null then 1 else 0 end) +
         (case when col3 is not null then 1 else 0 end) +
         (case when col4 is not null then 1 else 0 end)
       ) as occurrences
from t;

Upvotes: 1

Related Questions