I have to write a very interesting query which calculates null values and rows with values

Question

I have a scenario where i need to calculate number of total values and number of null values against each ID each ID column have a number of rows

Summarizing Data

- ID     Col1     Col2    Col3    Col4    Col5    Col6
 - 132    12       0.5      0      Null     0.3     1.5
 - 132    Null     0.5      0      Null     0.3     1.5
 - 132    1        0.5      Null   Null     0.3     1.5
 - 132    2        0.5      0      0.3      1.5     Null  
 - 132    21       0.5      0      Null     0.3     1.5

 - 133    Null     Null     0      Null     Null    1.5
 - 133    12       0.5      0      Null     0.3     1.5
 - 133    Null     0.5      0      Null     0.3     1.5
 - 133    1        0.5      Null   Null     0.3     1.5
 - 133    2        0.5      0      0.3      1.5     Null  
 - 133    1        Null     0      Null     0.3     1.5
 - 133    Null     Null     0      Null     0.3     1.5

Summarizing Answer :I need to write a query which gives me the data like following

- ID      NullCount    ValuesCount
 - 132      7               21    
 - 133      15              27

I have a deployment quick help will be much appreciated.

Thanks

yes columns are fixed like 6 or more .... they remain same only number of rows can be different — Ali Shahbaz, Oct 31 '14 at 07:34
possible duplicate of [Oracle: How to count null and non-null rows](http://stackoverflow.com/questions/5512691/oracle-how-to-count-null-and-non-null-rows) — wolfgangwalther, Oct 31 '14 at 07:56

score 3 · Accepted Answer · edited May 23 '17 at 10:33

Adapted from Oracle: How to count null and non-null rows:

SELECT
  COUNT(Col1)+COUNT(Col2)+COUNT(Col3)+
  COUNT(Col4)+COUNT(Col5)+COUNT(Col6) AS ValuesCount,

  6*COUNT(*)-COUNT(Col1)-COUNT(Col2)-COUNT(Col3)-
  COUNT(Col4)-COUNT(Col5)-COUNT(Col6) AS NullCount
FROM data
GROUP BY id

COUNT(ColX) only counts NOT NULL values. Adding those for all six columns equals ValuesCount, of course.
COUNT(*) counts all rows, even if all columns within one row were NULL. Multiply by 6 for the total number of cells and then subtract all NOT NULL values to get the NULL count.

Kim Berg Hansen · Answer 2 · 2014-10-31T08:07:57.490

COUNT counts non-null values. So the ValueCount is easy - add the counts of each column.

For the NullCount you can use CASE or other similar logic. Or you can use NVL2 function to turn anything NOT NULL into NULL and any NULL into something NOT NULL (like a constant.)

select id
     , count(nvl2(col1,null,1)) + count(nvl2(col2,null,1)) +
       count(nvl2(col3,null,1)) + count(nvl2(col4,null,1)) +
       count(nvl2(col5,null,1)) + count(nvl2(col6,null,1)) nullcount
     , count(col1) + count(col2) + count(col3) +
       count(col4) + count(col5) + count(col6) valuecount
  from tab
 group by id
 order by id
/

EDIT:

An alternative method is to UNPIVOT the data (which can be done using UNPIVOT or alternative unpivoting methods.)

select id
     , count(nvl2(col_value,null,1)) nullcount
     , count(col_value) valuecount
  from tab
 unpivot include nulls(
   col_value for col_name in (
      col1 as 'col1'
    , col2 as 'col2'
    , col3 as 'col3'
    , col4 as 'col4'
    , col5 as 'col5'
    , col6 as 'col6'
   )
 )
 group by id
 order by id
/

But when you have a fixed number of columns, it might be overkill to do unpivoting, when you can get the desired result in a single GROUP BY operation just by doing some copy-paste to specify all columns like my first example.

score 0 · Answer 3 · answered Oct 31 '14 at 07:58

-- The data

SQL> WITH DATA AS(
  2    SELECT 132 ID,  12  Col1,0.5  Col2,0  Col3,NULL Col4,0.3 Col5,1.5 Col6 FROM dual UNION ALL
  3    SELECT 132 ,   NULL ,    0.5  ,    0   ,   NULL  ,   0.3  ,   1.5 FROM dual UNION ALL
  4    SELECT 132 ,   1   ,     0.5  ,    NULL,   NULL ,    0.3 ,   1.5 FROM dual UNION ALL
  5    SELECT 132 ,   2  ,      0.5  ,    0 ,     0.3  ,    1.5 ,    NULL   FROM dual UNION ALL
  6    SELECT 132 ,   21 ,      0.5  ,    0  ,    NULL ,    0.3 ,    1.5 FROM dual UNION ALL
  7    SELECT 133 ,   NULL ,    NULL ,    0  ,    NULL ,    NULL ,   1.5 FROM dual UNION ALL
  8    SELECT 133 ,   12  ,     0.5  ,    0  ,    NULL ,    0.3 ,    1.5 FROM dual UNION ALL
  9    SELECT 133 ,   NULL ,    0.5  ,    0  ,    NULL ,    0.3  ,   1.5 FROM dual UNION ALL
 10    SELECT 133 ,   1  ,      0.5 ,     NULL,   NULL ,    0.3  ,   1.5 FROM dual UNION ALL
 11    SELECT 133 ,   2  ,      0.5  ,    0  ,    0.3   ,   1.5 ,    NULL   FROM dual UNION ALL
 12    SELECT 133 ,   1  ,      NULL ,    0 ,     NULL  ,   0.3 ,    1.5 FROM dual UNION ALL
 13    SELECT 133 ,   NULL  ,   NULL ,    0 ,     NULL  ,   0.3  ,   1.5 FROM dual
 14    )

--The query

 15    SELECT ID, sum(cnt) as nullcount from(
 16    SELECT ID, count(*) cnt FROM DATA
 17    WHERE col1 IS NULL
 18    GROUP BY ID
 19    UNION ALL
 20    SELECT ID, count(*) FROM DATA
 21    WHERE col2 IS NULL
 22    GROUP BY ID
 23    UNION ALL
 24    SELECT ID, count(*) FROM DATA
 25    WHERE col3 IS NULL
 26    GROUP BY ID
 27    UNION ALL
 28    SELECT ID, count(*) FROM DATA
 29    WHERE col4 IS NULL
 30    GROUP BY ID
 31    UNION ALL
 32    SELECT ID, count(*) FROM DATA
 33    WHERE col5 IS NULL
 34    GROUP BY ID
 35    UNION ALL
 36    SELECT ID, count(*) FROM DATA
 37    WHERE col6 IS NULL
 38    GROUP BY ID
 39  )
 40  GROUP BY ID
 41  /

                  ID            NULLCOUNT
-------------------- --------------------
                 132                    7
                 133                   15

SQL>

Similarly for the valuescount, write an in-line view.

score 0 · Answer 4 · answered Oct 31 '14 at 08:09

select id, 6 * cnt_all - cnt_not_null, cnt_not_null from (
  select id, count(*) cnt_all, 
             count(col1)+count(col2)+count(col3)+count(col4)+count(col5)+count(col6) cnt_not_null
  from tab group by id
);

6 is a number of columns

COUNT(col) doesn't take into consideration NULL values

COUNT(*) counts all values in a column including NULLS

score 0 · Answer 5 · edited Nov 06 '14 at 13:58

0

select id, 
       (count(*) * 6) - (count(Col1) + count(Col2) + count(Col3) + count(Col4)+count(Col5)+count(Col6)) as NullCount, 
       count(Col1) + count(Col2) + count(Col3) + count(Col4)+count(Col5)+count(Col6) ValuesCount from 
       DATA 
group by id 
order by id;

edited Nov 06 '14 at 13:58

ngrashia

9,869
5
43
58

answered Oct 31 '14 at 08:23

kemals

216
1
7

score -1 · Answer 6 · answered Oct 31 '14 at 08:02

A horrible, but working version:

select distinct N.ID, 
(select count(*) from tab N2 where N2.ID = N.ID and col1 is null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col2 is null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col3 is null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col4 is null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col5 is null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col6 is null) 'Null'
, 
(select count(*) from tab N2 where N2.ID = N.ID and col1 is not null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col2 is not null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col3 is not null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col4 is not null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col5 is not null)
+ (select count(*) from tab N2 where N2.ID = N.ID and col6 is not null) 'Not null'
from tab N

I have to write a very interesting query which calculates null values and rows with values

6 Answers6

Linked