how to count rows based on column values null and not null

Question

May be this question is a duplicate of another, I already explored couple of similar questions here but I didn't find similar one. Please suggest if you find a link to similar question.

My problem is, I have a table say CLIENTS as below

BRANCH     CLNTID      ACCNT      FACID
------ ---------- ---------- ----------
   201      10001     123400     110021

   201      10001     123401

   201      10001     123402     110023

   201      10001     123403

   201      10001     123404     110025

   201      10001     123405

   201      10001     123406     110027

   201      10001     123407     110028

so on... many rows. Now I want to write a query to give output like this

Branch   clntid   facid_null   facid_not_null

 201       10001      3              5

I want to find facid colmun count for facid=null and facid !=null for each branch and each clntid.

I wrote the below query but its fetching only one count either facid is null or facid is not null.

select branch,clntid,count(*)
from clnt
where facid is null
group by branch, clntid;

Please help me to find both counts in a single query using GROUP BY and as well as OVER (PARTITION BY ) clauses.

Thanks in advance. Vivek.

Ronnis · Accepted Answer · 2014-10-19 09:41:09Z

1

select branch
      ,clntid
      ,count(*)                as num_rows
      ,count(facid)            as not_nulls
      ,count(*) - count(facid) as nulls
  from clnt
 group 
    by branch
      ,clntid;

answered Oct 19, 2014 at 9:41

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

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

1 Comment

vc 74 Over a year ago

+1 for making me remember count only counts the non null values

Rob van Wijk · Accepted Answer · 2014-10-19 09:39:00Z

The aggregate function COUNT counts all not null occurrences, so you can simply use count(facid) to count the facid_not_null column and you can use a similar technique and first swap the null and not null for the facid_null column. Here is a working example:

SQL> create table clnt (branch,clntid,accnt,facid)
  2  as
  3  select 201, 10001, 123400, 110021 from dual union all
  4  select 201, 10001, 123401, null from dual union all
  5  select 201, 10001, 123402, 110023 from dual union all
  6  select 201, 10001, 123403, null from dual union all
  7  select 201, 10001, 123404, 110025 from dual union all
  8  select 201, 10001, 123405, null from dual union all
  9  select 201, 10001, 123406, 110027 from dual union all
 10  select 201, 10001, 123407, 110028 from dual
 11  /

Table created.

SQL> select branch
  2       , clntid
  3       , count(case when facid is null then 1 end) facid_null
  4       , count(facid) facid_not_null
  5    from clnt
  6   group by branch
  7       , clntid
  8  /

    BRANCH     CLNTID FACID_NULL FACID_NOT_NULL
---------- ---------- ---------- --------------
       201      10001          3              5

1 row selected.

Collectives™ on Stack Overflow

how to count rows based on column values null and not null

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related