CODEWITHSUNDEEP
oracle-databasegroup-by
vivek
vivek

Reputation: 35

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

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.

Upvotes: 1

Views: 1616

Answers (2)

Ronnis
Ronnis

Reputation: 12843

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

Upvotes: 1

Rob van Wijk
Rob van Wijk

Reputation: 17705

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.

Upvotes: 0

Related Questions