How calculate the percentage of area
Hi,
I have 4 tables:
Tab AREA_TI
Code:
LS_ID FL AREA
EMS00046 P01 2.995,09
EMS00046 P02 0,78
EMS00046 PTE 1.435,48
EMS00046 S01 1.684,80
EMS00046 S02 1.694,12
EMS00050 P01 1.111,60
EMS00050 P02 1.054,16
EMS00050 P03 1.117,05
EMS00050 P04 1.087,24
EMS00050 P05 1.117,85
EMS00050 P06 646,99
EMS00050 P07 249,48
EMS00050 PTE 593,35
EMS00050 S01 91,15
EMS00011 P02 446,90
EMS00021 PTE 0,71
SUM AREA_TI is:
Code:
LS_ID SUM(AREA)
EMS00011 446,90
EMS00021 0,71
EMS00046 7.810
EMS00050 7.068
Tab AREA_T0
Code:
LS_ID FL AREA
EMS00046 PTE 15,48
EMS00050 PTE 15,33
00001156 P01 3.379,82
00001156 P02 3.731,25
00001156 P03 2.686,88
00001156 PTE 2.279,49
00001156 S01 7.341,05
SUM AREA_T0 is:
Code:
LS_ID SUM(AREA)
00001156 19.418
EMS00046 15,48
EMS00050 15,33
Tab AREA_FI
Code:
LS_ID FL AREA
04600411 P01 785,64
04600411 P02 829,72
04600411 P03 832,36
04600411 PCO 11,88
04600411 PTE 473,91
04600411 S01 1.740,42
SUM AREA_FI is:
Code:
LS_ID SUM(AREA)
04600411 4.673,93
Tab AREA_EM
Code:
LS_ID FL AREA
00307374 P01 4.269,59
00307374 P02 8.362,79
00307374 P03 770,96
00307374 PTE 7.292,11
00307374 S01 1.425,90
00602037 P01 3.364,47
00602037 PTE 9.764,71
00602037 S01 75,17
SUM AREA_EM is:
Code:
LS_ID SUM(AREA)
00307374 22.121
00602037 13.204
I'd like to get these results:
Code:
LS_ID AREA_TI AREA_T0 AREA_FI AREA_EM AREA_TOT PERC_T0 PERC_FI PERC_TI PERC_EM
EMS00011 446,90 0 0 0 446,90 1
EMS00021 0,71 0 0 0 0,71 1
EMS00046 7810 15,48 0 0 7825,75 0,00197 0,99803
EMS00050 7068 15,33 0 0 7083,33 0,00216 0,99784
00001156 0 19418 0 0 19418 1
04600411 0 0 4673 0 4673 1
00307374 0 0 0 22121 22121 1
00602037 0 0 0 13204 13204 1
PERC_T0=AREA_T0/AREA_TOT
PERC_FI=AREA_FI/AREA_TOT
PERC_TI=AREA_TI/AREA_TOT
PERC_EM=AREA_EM/AREA_TOT
I tried this:
CREATE OR REPLACE VIEW PERC_AREA ( LS_ID,
AREA_T0, AREA_FI, AREA_EM, AREA_TI,
AREA_TOT, PERC_T0, PERC_FI, PERC_TI,
PERC_EM ) AS
SELECT A.LS_ID,
SUM(nvl(A.AREA,0)),
SUM(nvl(B.AREA,0)),
SUM(nvl(C.AREA,0)),
SUM(nvl(D.AREA,0)),
SUM(NVL(A.AREA,0)+NVL(B.AREA,0)+NVL(C.AREA,0)+NVL(D.AREA,0)),
SUM(A.AREA)/SUM(NVL(A.AREA,0)+NVL(B.AREA,0)+NVL(C.AREA,0)+NVL(D.AREA,0)),
SUM(B.AREA)/SUM(NVL(A.AREA,0)+NVL(B.AREA,0)+NVL(C.AREA,0)+NVL(D.AREA,0)),
SUM(C.AREA)/SUM(NVL(A.AREA,0)+NVL(B.AREA,0)+NVL(C.AREA,0)+NVL(D.AREA,0)),
SUM(D.AREA)/SUM(NVL(A.AREA,0)+NVL(B.AREA,0)+NVL(C.AREA,0)+NVL(D.AREA,0))
FROM AREA_T0 A, AREA_FI B,AREA_TI C, AREA_EM D
WHERE A.LS_ID=B.LS_ID(+)
AND a.LS_ID=C.LS_ID(+)
AND a.LS_ID=D.LS_ID(+)
GROUP BY A.LS_ID
How can I create a oracle view to get percentage of area FOR ALL LS_ID present in the 4 tables??
Thanks!!!
