How to exclude null values when using DECODE in SELECT query

user_17266973 0
Viewed 96
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.

oracle sql
2 Answers

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>
user_90979060

Yet you can use DECODE through connecting with SIGN and NVL2, which lets you substitution of both a null and as well as a non-null value, functions combination such as

SELECT e_id,
       SUM(DECODE(q_id, 13, SIGN(NVL2(a_value,1,0)+NVL2(r_pos_a_id,1,0)))) AS src_cnt,
       SUM(DECODE(q_id, 15, SIGN(NVL2(a_value,1,0)+NVL2(r_pos_a_id,1,0)))) AS tgt_cnt,
       SUM(DECODE(q_id, 14, SIGN(NVL2(a_value,1,0)+NVL2(r_pos_a_id,1,0)))) AS src_oth,
       SUM(DECODE(q_id, 16, SIGN(NVL2(a_value,1,0)+NVL2(r_pos_a_id,1,0)))) AS tgt_oth
  FROM e_table
 GROUP BY e_id;

Demo

where if a_value or r_pos_a_id are non-null, then NVL2 function would return 1 , otherwise would return 0 respectively. There would be no problem (the total value would be 0 or 1) unless both are non-null. In this case, the SIGN function is used in order to count each non-null combination once.

user_58413060
Related