current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
1  
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 yesterday
    
you can use count(c1), count(c2),... it auto ignores nulls –  Jenn 22 hours ago

2 Answers 2

up vote 0 down vote accepted

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;
share|improve this answer
    
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. –  chrislott 18 mins ago
up vote 0 down vote 
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.

share|improve this answer
    
Do you remember that NULL is not equal to NULL? –  PM 77-1 23 hours ago
    
This ignores the part of my question about separating by form type, using values in column 'ftype' –  chrislott 14 mins ago
    
Is that better? –  bowlturner 41 secs ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.