All,
ive 2 tables as below
tbl1:
location locID Score CNT
===================================================
TX 7015 20 10
MA 6113 30 45
MN 8410 15 21
tbl2:
location locID Score CNT
==============================================
NM 3542 17 9
NY 2991 35 25
Ima trying to join 2 tables so i produce one summarized result as shown. my query is similar to the below pseudocode but the problem we cant nest SUM function.
SELECT LISTAGG(LOCATION) AS LOCATIONS , LISTAGG(LOCID) AS ID, SUM((VALS*CNT)/SUM(CNT)) FINAL_VALS, SUM(CNT) FROM
(
SELECT LISTAGG(LOCATION) , LISTAGG(LOCID), SUM((SCORE*CNT)/SUM(CNT)) VALS, SUM(CNT) CNT FROM TBL1 --<< grouped into 1 record from tbl1
UNION ALL
SELECT LISTAGG(LOCATION) , LISTAGG(LOCID), SUM((SCORE*CNT/SUM(CNT)) VALS, SUM(CNT) CNT FROM TBL2 --<< grouped into 1 record from tbl2
) x
where x.VALS is not null;
-------------------
in Oracle 12c
any great suggestion is appreciated...thanks