sliders_alpha sliders_alpha - 3 months ago 11
SQL Question

Group by a multiple select request from different tables

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.

EDIT : as requested here are example data and result



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