sliders_alpha - 1 year ago 52

SQL Question

so I made this :

`select (`

SELECT COUNT (*)

from TXN_TOTO

WHERE (CO1 = '1L' OR CO1 = '1') AND OP1 in('P3', 'R1')

) as A,

(

SELECT COUNT (*)

from TXN_TITI

WHERE (CO1 = '1L' OR CO1 = '1') AND OP1 in('P3', 'R1') AND STAT = 6

) as B,

(

SELECT COUNT (*)

from T_TITI tti inner join T_TATA ttdi

ON tti.ID_DINT = ttdi.ID_DINT

WHERE (CO1 = '1L' AND OP1 in('01', '04', 'Z8')) OR (CO1 = '1' AND OP1 in('P3', 'R1')) AND COM = 'O'

) as C

FROM DUAL;

I get result who look like this :

`A | B | C`

----------

7 | 1 | 9

Both TXN_TOTO and TXN_TITI table have a 'cent' column, I'd like to filter on that in order to get

`CENT | A | B | C`

----------------

0 | 2 | 0 | 0

1 | 2 | 1 | 4

2 | 3 | 0 | 5

Since I getting my data from 2 different tables I really don't see how to do it.

Thanks.

`TXN_TOTO`

ID_DINT | CO1 | OP1 | DID_CENT

------------------------------

1 2L Z3 088

2 1L 1 089

3 1 P3 155

4 1L Z3 155

5 1L 1 077

6 1 P3 077

7 1L Z3 077

8 1L 1 077

9 1 P3 022

TXN_TITI

ID_DINT | CO1 | OP1 | DID_CENT |STAT

------------------------------------

1 2L Z3 088 6

2 1L 1 089 6

3 1 P3 155 6

4 1L Z3 155 6

5 1L 1 077 6

6 1 P3 077 6

7 1L Z3 077 6

8 1L Z8 077 6

9 1 R1 022 5

TXN_TATA

ID_DINT | COM |

---------------

1 O

2 O

3 O

4 O

5 N

6 O

7 O

8 O

9 O

Expected results :

DID_CENT | A | B | C

155 1 1 0

077 1 1 1

022 1 0 0

A is only computed from TXN_TOTO

B is only computed from TXN_TITI, only difference is the stat column

C is a join of TITI and TATA, you need to have a O in TATA

Answer Source

```
select coalesce(A.DID_CENT,B.DID_CENT,C.DID_CENT) DID_CENT,
nvl(sum(A.cnt),0) A, nvl(sum(B.cnt),0) B, nvl(sum(C.cnt),0) C
from
(
SELECT DID_CENT, COUNT (*) cnt
from TXN_TOTO
WHERE (CO1 = '1L' OR CO1 = '1') AND OP1 in('P3', 'R1')
GROUP BY DID_CENT
) A
FULL JOIN
(
SELECT DID_CENT, COUNT (*) cnt
from TXN_TITI
WHERE (CO1 = '1L' OR CO1 = '1') AND OP1 in('P3', 'R1') AND STAT = 6
GROUP BY DID_CENT
) B ON A.DID_CENT=B.DID_CENT
FULL JOIN
(
SELECT DID_CENT, COUNT (*) cnt
from TXN_TITI tti inner join TXN_TATA ttdi
ON tti.ID_DINT = ttdi.ID_DINT
WHERE (CO1 = '1L' AND OP1 in('01', '04', 'Z8')) OR (CO1 = '1' AND OP1 in('P3', 'R1'))
GROUP BY DID_CENT
) C ON B.DID_CENT=C.DID_CENT
GROUP BY coalesce(A.DID_CENT,B.DID_CENT,C.DID_CENT)
```