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.
CodePudding user response：
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>
CodePudding user response：
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)) SIGN(NVL2(r_pos_a_id,1,0)))) AS src_cnt,
SUM(DECODE(q_id, 15, SIGN(NVL2(a_value,1,0)) SIGN(NVL2(r_pos_a_id,1,0)))) AS tgt_cnt,
SUM(DECODE(q_id, 14, SIGN(NVL2(a_value,1,0)) SIGN(NVL2(r_pos_a_id,1,0)))) AS src_oth,
SUM(DECODE(q_id, 16, SIGN(NVL2(a_value,1,0)) SIGN(NVL2(r_pos_a_id,1,0)))) AS tgt_oth
FROM e_table
GROUP BY e_id;