Count non-null values in columns grouped by row type value from another column

Question

I am working with an Oracle db, trying to count non-null values in columns and report results by row types. Please help, I'm regularly humbled by the power of SQL.

I have a table of financial-reporting-form data: column 1 is the form type, columns 2..900 (seriously it's quite wide) have a null or a number. So I'm trying to characterize by form type the non-null values.

Sample fragment of table t1 - only shows 2 but there are 8 form types:

ftype   c1     c2     c3     c4      c5
-----   --     --     --     --      --
a       1      2      3      null    null 
b       null   null   null   4       5
b       null   null   null   44      55

The desired report is below; the numbers are the non-null counts from the sample table above:

        a      b
        --     --
c1      1      0
c2      1      0
c3      1      0
c4      0      2
c5      0      2

With this report we'll be able to say "Hey, column 4 is never populated for Form A".

If I went at the problem in a procedural programming language I'd write something like the following. Then I'd emit the results as a big table:

for FORM in FormA .. FormH
    for COLUMN in Col1 .. Col900
        select count(*) from t1 where ftype = '${FORM}' and ${COLUMN} is not null;
    end for
end for

These posts at SO pointed me to the CASE construct but are not quite the same:

Count number of NULL values in each column in SQL

Counting non-null columns in a rather strange way

I also created a SQL Fiddle for this question: http://sqlfiddle.com/#!2/e4d43/2

What's the smart way? Do I have to write a huge number of SQL queries? Thanks in advance.

Update 1: Thanks for the quick replies. I just need the information, don't care if the report has the columns across and the types down, does it the solution much easier (i.e., no pivot required)? The following would be just fine by me, it would get very very wide but we'll cope:

     c1    c2    c3    c4    c5
     --    --    --    --    --
 a    1     1     1     0     0
 b    0     0     0     2     2

2 components you'll need to know for this...pivot (in your case, unpivot) is the term for bringing out the columns into rows. From there you'll need to do the null value count logic. — Twelfth
– Twelfth, Commented Jul 30, 2014 at 19:55
you can use count(c1), count(c2),... it auto ignores nulls — Jenn
– Jenn, Commented Jul 30, 2014 at 21:30

Gordon Linoff · Accepted Answer · 2014-07-31 19:51:33Z

0

If you know the form types, then you can do this with an aggregation query:

select ftype,
       count(c1) as c1,
       count(c2) as c2,
       count(c3) as c3,
       count(c4) as c4
from t1
group by ftype;

Note that count(<expression>) counts the non-NULL values in the <expression>. This is the easiest result. If you want to get a separate row for each column and a separate column for each ftype, the query is a bit more cumbersom. Here is one way:

select 'c1' as col,
       sum(case when ftype = 'a' and c1 is not null then 1 else 0 end) as a,
       sum(case when ftype = 'b' and c1 is not null then 1 else 0 end) as b
from table1
union all
select 'c2' as col,
       sum(case when ftype = 'a' and c2 is not null then 1 else 0 end) as a,
       sum(case when ftype = 'b' and c2 is not null then 1 else 0 end) as b
from table1
union all
select 'c3' as col,
       sum(case when ftype = 'a' and c3 is not null then 1 else 0 end) as a,
       sum(case when ftype = 'b' and c3 is not null then 1 else 0 end) as b
from table1
union all
select 'c4' as col,
       sum(case when ftype = 'a' and c4 is not null then 1 else 0 end) as a,
       sum(case when ftype = 'b' and c4 is not null then 1 else 0 end) as b
from table1;

edited Jul 31, 2014 at 19:51

answered Jul 31, 2014 at 12:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chrisinmtown Over a year ago

In the first code snippet, pls change "from table t1" to just "from t1" and then it works excellently. In the second snippet pls add "from t1" after the second select and before the second "union all", ditto for third and fourth selects, then that works quite well too. :) Thank you Gordon Linoff.

bowlturner · Accepted Answer · 2014-07-31 19:58:17Z

0

SELECT ftype, sum(case when c1 is null then 0 else 1) C1Used,
   sum(case when c2 is null then 0 else 1) C2Used,
   sum(....
FROM Form
Group by ftype;

is the way I'd do it. This would give you a count and zero would be "not used" Granted writing it for 900 columns, I think I'd write a query to write it out for me though.

edited Jul 31, 2014 at 19:58

answered Jul 30, 2014 at 19:59

bowlturner

2,0264 gold badges26 silver badges38 bronze badges

3 Comments

PM 77-1 Over a year ago

Do you remember that NULL is not equal to NULL?

chrisinmtown Over a year ago

This ignores the part of my question about separating by form type, using values in column 'ftype'

bowlturner Over a year ago

@chrislott Is that better?

Collectives™ on Stack Overflow

Count non-null values in columns grouped by row type value from another column

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related