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, Jul 30 '14 at 19:55
you can use `count(c1), count(c2),...` it auto ignores nulls — Jenn, Jul 30 '14 at 21:30

bowlturner · Answer 1 · 2014-07-31T19:58:17.790

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 '14 at 19:58

answered Jul 30 '14 at 19:59

bowlturner

1,968
4
23
35

Do you remember that `NULL` is **not** equal to `NULL`? – PM 77-1 Jul 30 '14 at 20:04
This ignores the part of my question about separating by form type, using values in column 'ftype' – chrisinmtown Jul 31 '14 at 19:44
@chrislott Is that better? – bowlturner Jul 31 '14 at 19:59

Gordon Linoff · Accepted Answer · 2014-07-31T19:51:33.310

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;

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. — chrisinmtown, Jul 31 '14 at 19:41

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

2 Answers2