How to exclude null values when using DECODE in SELECT query

January 28, 2022

CREATE TABLE e_table (
    e_id        NUMBER(10),
    q_id        NUMBER(10),
    a_value     VARCHAR2(20),
    r_pos_a_id  NUMBER(10)
);

INSERT INTO e_table VALUES(11,13,null,null);
INSERT INTO e_table VALUES(11,15,null,null);
INSERT INTO e_table VALUES(11,14,null,null);
INSERT INTO e_table VALUES(11,16,null,null);
INSERT INTO e_table VALUES(11,13,null,992);
INSERT INTO e_table VALUES(11,13,null,637);
INSERT INTO e_table VALUES(11,15,null,637);
INSERT INTO e_table VALUES(11,14,'Manual',null);

select e_id,
sum(decode(q_id,13,1,0)) src_cnt,
sum(decode(q_id,15,1,0)) tgt_cnt,
sum(decode(q_id,14,1,0)) src_oth,
sum(decode(q_id,16,1,0)) tgt_oth
from e_table
group by e_id;

Expected output:

+--------+-----------+-----------+----------+----------+
| E_ID   | SRC_CNT   | TGT_CNT   | SRC_OTH  |  TGT_OTH |
+--------+-----------+-----------+----------+----------+
|     11 |         2 |         1 |        1 |        0 |
+--------+-----------+-----------+----------+----------+

Currently, I am doing the sum for all the occurrences of q_id in the table. Say for q_id 13 it’s coming as 3 times in the table e_table and likewise for all the q_id i.e 14, 15, and 16. But I want to exclude the null values. If a_value and r_pos_a_idare null for that particular id then I have to exclude that entry from my occurrences count. For instance, q_id 13 is coming as thrice but it should count only for the ones which have either a_value or r_pos_a_id and exclude that has both a_value and r_pos_a_id as null. And the same I have to do for all the q_id’s.

>Solution :

Add some more conditions into CASE. Why CASE and not DECODE? Because it allows flexibility.

SQL> select e_id,
  2    sum(case when q_id = 13 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) src_cnt,
  3    sum(case when q_id = 15 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) tgt_cnt,
  4    sum(case when q_id = 14 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) src_oth,
  5    sum(case when q_id = 16 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) tgt_oth
  6  from e_table
  7  group by e_id;

      E_ID    SRC_CNT    TGT_CNT    SRC_OTH    TGT_OTH
---------- ---------- ---------- ---------- ----------
        11          2          1          1          0

SQL>