Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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


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

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Your solution is a litle bit flaw, since it is using a "driving" table (AREA_T0) to get ALL of the LS_IDs that will be listed, meaning that it will only list those that exists on it.

    This sounds like a good scenario to use a Materialized View.

    Say, you create a MV named mv_xxx as below:

    Code:
    
    create materialized view mv_xxx
     refresh start with sysdate next trunc(sysdate+1)
     enable query rewrite
    as
    select ls_id, sum(area) area
      from area_ti
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_t0
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_fi
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_em
     group by ls_id
    
    .. then, to obtain the results you want, you can:

    Code:
    
    select x.ls_id,
           area_ti.sum area_ti,
           area_t0.sum area_t0,
           area_fi.sum area_fi,
           area_em.sum area_em,
           area_tot.sum area_tot,
           area_t0.sum/area_tot.sum perc_t0,
           area_fi.sum/area_tot.sum perc_fi,
           area_ti.sum/area_tot.sum perc_ti,
           area_em.sum/area_tot.sum perc_em,
           round(area_tot.sum*100/area_gen.sum,2) perc_lsid
      from (select distinct ls_id
              from mv_xxx) x,
           (select ls_id, sum(area) sum
              from area_ti
             group by ls_id) area_ti,
           (select ls_id, sum(area) sum
              from area_t0
             group by ls_id) area_t0,
           (select ls_id, sum(area) sum
              from area_fi
             group by ls_id) area_fi,
           (select ls_id, sum(area) sum
              from area_em
             group by ls_id) area_em,
           (select ls_id, sum(area) sum
              from mv_xxx
              group by ls_id) area_tot,
           (select sum(area) sum
              from mv_xxx) area_gen
     where x.ls_id = area_ti.ls_id(+)
       and x.ls_id = area_t0.ls_id(+)
       and x.ls_id = area_fi.ls_id(+)
       and x.ls_id = area_em.ls_id(+)
       and x.ls_id = area_tot.ls_id(+)
    
    OR

    If you dont want to use a Materialized View, you can:

    Code:
    
    with x as (
    select ls_id, sum(area) area
      from area_ti
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_t0
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_fi
     group by ls_id
    union all
    select ls_id, sum(area) area
      from area_em
     group by ls_id
              )
    select x.ls_id,
           area_ti.sum area_ti,
           area_t0.sum area_t0,
           area_fi.sum area_fi,
           area_em.sum area_em,
           area_tot.sum area_tot,
           area_t0.sum/area_tot.sum perc_t0,
           area_fi.sum/area_tot.sum perc_fi,
           area_ti.sum/area_tot.sum perc_ti,
           area_em.sum/area_tot.sum perc_em,
           round(area_tot.sum*100/area_gen.sum,2) perc_lsid
      from (select distinct ls_id
              from x) x,
           (select ls_id, sum(area) sum
              from area_ti
             group by ls_id) area_ti,
           (select ls_id, sum(area) sum
              from area_t0
             group by ls_id) area_t0,
           (select ls_id, sum(area) sum
              from area_fi
             group by ls_id) area_fi,
           (select ls_id, sum(area) sum
              from area_em
             group by ls_id) area_em,
           (select ls_id, sum(area) sum
              from x
              group by ls_id) area_tot,
           (select sum(area) sum
              from x) area_gen
     where x.ls_id = area_ti.ls_id(+)
       and x.ls_id = area_t0.ls_id(+)
       and x.ls_id = area_fi.ls_id(+)
       and x.ls_id = area_em.ls_id(+)
       and x.ls_id = area_tot.ls_id(+)
    
    In either, PERC_LSID represent the percentage acording to the total sum of the area total.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    I tried this:

    CREATE OR REPLACE VIEW PERC_AREA
    (LS_ID,
    AREA_TI,
    AREA_T0,
    AREA_FI,
    AREA_EM,
    AREA_T0T,
    PERC_T0,
    PERC_FI,
    PERC_TI,
    PERC_EM)
    AS
    SELECT F.LS_ID,
    SUM(nvl(C.AREA,0)),
    SUM(nvl(A.AREA,0)),
    SUM(nvl(B.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,
    (SELECT ls_id FROM area_to
    UNION
    SELECT ls_id FROM area_fi
    UNION
    SELECT ls_id FROM area_ti
    UNION
    SELECT ls_id FROM area_em) F
    WHERE F.LS_ID=A.LS_ID(+)
    AND F.LS_ID=B.LS_ID(+)
    AND F.LS_ID=C.LS_ID(+)
    AND F.LS_ID=D.LS_ID(+)
    GROUP BY F.LS_ID;

    What do you think about solution??

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •