By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE e_table (
e_id NUMBER(10),
q_id NUMBER(10),
a_value VARCHAR2(20),
r_pos_a_id NUMBER(10)
);
BEGIN
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);
END;
/
1 rows affected
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;
| E_ID | SRC_CNT | TGT_CNT | SRC_OTH | TGT_OTH |
|---|---|---|---|---|
| 11 | 2 | 1 | 1 | 0 |