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

    Unanswered: I have any problem with calculate percentages

    Hi,
    I’ve any problem to calcolate the percentage on UL.
    I’ve these tables:
    RM:
    BL_ID…………………VARCHAR2(8) PK1 (building)
    FL_ID………………… VARCHAR2(4) PK2 (floor)
    RM_ID…………………VARCHAR2(8) PK3 (room)
    UL_ID…………………VARCHAR2(30) (locativa unit)
    RM_USE………………VARCHAR2(16)
    DP_ID…………………VARCHAR2(16)
    DV_ID..………………VARCHAR2(16)
    AREA…………………NUMBER
    RM_SPC:
    BL_ID……………....... VARCHAR2(8) FK ON BL_ID(RM)
    BL_SERV……………….VARCHAR2(8)
    FL_ID…………………VARCHAR2(4) FK ON FL_ID (RM)
    RM_ID………………..VARCHAR2(8) FK ON RM_ID (RM)
    RM_SERV……………VARCHAR2(8)
    FL_SERV …………….VARCHAR2(4)
    AUTONUM…………...NUMBER(38)

    BL_ID...........FL_ID..............RM_ID.......... .......RM_USE...........UL_ID
    BB01.............001...............001_022........ ............PARK.................29
    BB01.............001...............001_026........ ............PARK.................28
    BB01.............00T...............00T_049........ ...........PARK..................28
    BB01.............01M..............01M_024......... .........PARK..................28
    AA01.............01S..............01S_002......... ...........PARK..................33
    AA01.............01S..............01S_025......... ...........PARK..................36

    BL_ID............BL_SERV.......FL_ID...........FL_ SERV........RM_ID.............RM_SERV
    BB01..............BB01.............001............ ...001...................001_029..........001_026
    BB01..............BB01.............001............ ...001...................002_001..............001_ 022A
    BB01..............BB01.............001............ ...001...................002_001..............001_ 026
    BB01..............BB01.............001............ ...001....................002_002.............001_ 026
    AA01..............AA01.............01S............ ..01S...................01S_005.............01S_00 2
    AA01..............AA01.............01S............ ...01S...................01S_005.............01S_0 25
    AA01..............AA01.............01S............ ...01S...................01S_005A...........01S_00 2
    AA01..............AA01.............01S............ ...01S...................01S_005A...........01S_02 5
    I created these views:
    CREATE OR REPLACE VIEW RM_VIEW ( BL_SERV,
    FL_SERV, RM_SERV, RM_USE, UL_ID
    ) AS select BL_ID as bl_serv,
    FL_ID as fl_serv,
    RM_ID as rm_serv,
    rm_use,
    ul_id
    from RM

    CREATE OR REPLACE VIEW RM_SPC_VIEW ( UL_ID,
    DP_ID, DV_ID, AREA, BL_ID,
    FL_ID, RM_ID, BL_SERV, FL_SERV,
    RM_SERV ) AS select rm.ul_id,rm.dp_id, rm.dv_id, rm.area, rm_spc.bl_id,
    rm_spc.fl_id, rm_spc.rm_id, rm_spc.bl_serv,
    rm_spc.fl_serv, rm_spc.rm_serv
    from rm, rm_spc
    where rm.bl_id=rm_spc.bl_id
    and rm.fl_id=rm_spc.fl_id
    and rm.rm_id=rm_spc.rm_id

    Now for example:
    Select * from RM_VIEW where bl_serv=’AA01’ and rm_use=’PARK’

    BL_SERV………FL_SERV……RM_SERV……RM_USE….UL_ID
    AA01…………….00S………….00S_001………..PARK………33

    SELECT *
    FROM RM_SPC_VIEW
    WHERE BL_ID=’AA-01’

    BL_ID………FL_ID……RM_ID…….....DP_ID….DV_ID……..UL_ID…… ...AREA
    AA01…………00S………01S_044…......…CCC……DDD………...34……... ..7.15
    AA01…………00S………01S_045….....…CCC….…DDD………...34……... ..52.68
    AA01…………00S………01S_049….....…CCC….…DDD……..….35……... ..14.32
    AA01…………00S………01S_041.....……CCC….…DDD………...37……... .18.76
    AA01…………00S………01S_040….....…CCC….…DDD………...37……... .16.49
    AA01…………00S………01S_042….....…CCC….…DDD………...37……... .34.03

    FOR UL_ID=33 AND USE=PARK IN RM_VIEW I HAVE 3 UL_ID (34,35,37)
    UL_ID=34 has 59.93 mq (7.15+52.68)
    Total area for ul_id=33 is 143.43
    (59.83*100)/143.43 = 41.71%

    I’d like to have this view:

    BL_ID…..FL_ID…..RM_ID…..AREA_TOT…..UL_ID…..PERCENT
    AA01……00S……..01S_044………59.93………34………......41.71
    AA01……00S……..01S_044………14.32………35………......9.98
    AA01……00S……..01S_044………69.28………37………......48.30

    I tried with this view:
    select A.UL_ID,a.BL_ID,A.FL_ID,A.RM_ID,AREA_TOT, (A.AREA_TOT/B.TOTAL_AREA)*100 as PERCENT
    from
    (select UL_ID, BL_ID, FL_ID, RM_ID, sum(AREA) as AREA_TOT
    from RM_SPC_VIEW
    group by UL_ID, BL_ID, FL_ID, RM_ID
    ) A,
    (
    select sum(AREA) as TOTAL_AREA
    from RM_SPC_VIEW
    ) B

    but I get error values.

    I know that I could use RATIO_TO_REPORT() analytical function (how?)

    How can I get this view??
    Or Can I create PL/SQL procedure to update existing table?

    THANKS IN ADVANCE!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: I have any problem with calculate percentages

    (Funny how often people write things like "but I get error values", and then don't say what errors!)

    A simple example using the EMP table shows that your current approach should work:

    PHP Code:
       1 select deptnodept_saltot_saldept_sal/tot_sal*100
      2  from
      3  
    select deptnosum(saldept_sal
      4    from emp
      5    group by deptno
      6  
    a,
      
    7  select sum(saltot_sal
      8    from emp
      9  
    b;

        
    DEPTNO   DEPT_SAL    TOT_SAL DEPT_SAL/TOT_SAL*100
    ---------- ---------- ---------- --------------------
            
    10       8750      29025           30.1464255
            20      10875      29025           37.4677003
            30       9400      29025           32.3858742 
    Or using RATIO_TO_REPORT:
    PHP Code:
      1 select deptnodept_sal, (ratio_to_report(dept_salover ())*100 as pct
      2  from
      3  
    (
      
    4  select deptnosum(sal) as dept_sal
      5  from emp
      6  group by deptno
      7 
    );

        
    DEPTNO   DEPT_SAL        PCT
    ---------- ---------- ----------
            
    10       8750 30.1464255
            20      10875 37.4677003
            30       9400 32.3858742 
    The second SQL is more efficient, as it only processes the EMP table once.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    OK, Thanks for your answer, this is correct for table EMP but not for me:

    Infact with this view I get these percent values:

    UL_ID.........BL_ID.......FL_ID........RM_ID.....A REA_TOT.........PERCENT
    34..............AA01.........01S......01S_001..... .52,68................0,84
    37..............AA01.........01S......01S_006..... .68,06................1,08
    37..............AA01.........01S......01S_005A.... 32,98................0,52
    34..............AA01.........01S......01S_003..... .7,15..................0,11
    37..............AA01.........01S......01S_005..... .37,52................0,60
    35..............AA01.........01S......01S_004..... .28,64................0,45

    This because in RM_SPC_VIEW I have more record of this

    For example: (see table RM)
    BL_SERV………FL_SERV……RM_SERV……RM_USE….UL_ID
    AA01…………….00S………….01S_002………..PARK………33

    for ul_id=33 I HAVE 3 UL_ID (34,35,37) (see VIEW RM_SPC_VIEW)

    BL_SERV………FL_SERV……RM_SERV……RM_USE….UL_ID
    AA01…………….00S………….01S_025………..PARK………36



    UL_ID.........BL_ID.......FL_ID........RM_ID.....A REA
    35..............AA01.........01S......01S_004..... .14.32
    37..............AA01.........01S......01S_005..... .18.76
    37..............AA01.........01S......01S_005A.... 16.49
    37..............AA01.........01S......01S_006..... .34.03

    for ul_id=33 I HAVE 2 UL_ID (35,37)
    Total area for ul_id=36 is 83.60

    In this case I have: for UL_ID=36

    AREA_TOT…..UL_ID…..PERCENT
    14.32............35.........17.13
    69.28............37.........82.87



    Have you any idea? or impossibile create this view?

    Thanks again!

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    May be I am near solution my problem:

    I created 2 views:

    CREATE OR REPLACE VIEW RM_VIEW_CP ( BL_SERV,
    FL_SERV, RM_SERV, RM_USE, UL_ID
    ) AS select "BL_SERV","FL_SERV","RM_SERV","RM_USE","UL_ID"
    from rm_view
    where rm_use='PARK'


    CREATE OR REPLACE VIEW RM_PA ( BL_ID,
    FL_ID, RM_ID, AREA, DP_ID,
    DV_ID, UL_ID, UL_ID_SERV, RM_USE
    ) AS select a.bl_id, a.fl_id, a.rm_id, a.area, a.dp_id, a.dv_id, a.ul_id, b.UL_ID ul_id_serv, b.rm_use
    from rm_spc_view a, rm_view_cp b
    where a.bl_serv=b.BL_SERV
    and a.FL_SERVE=b.FL_SERV
    and a.RM_SERV=b.RM_SERV
    group by a.bl_id, a.fl_id, a.rm_id, a.area, a.dp_id, a.dv_id, a.ul_id, b.UL_ID, b.rm_use
    order by 1

    VIEW RM_PA has these values for bl_id='AA01' (BUT ALSO OTHER VALUES)

    BL_ID.....FL_ID......RM_ID.....AREA........UL_ID.. ..UL_ID_SERVRM_USE
    AA01.......01S......01S_001....52,68.......34..... ..33.........PARK
    AA01.......01S......01S_003....7,15........34..... ..33.........PARK
    AA01.......01S......01S_004.....14,32.......35.... ...33.........PARK
    AA01.......01S......01S_004.....14,32.......35.... ...36.........PARK
    AA01.......01S......01S_005.....18,76.......37.... ...33.........PARK
    AA01.......01S......01S_005.....18,76.......37.... ...36.........PARK
    AA01.......01S......01S_005A....16,49.......37.... ...33.........PARK
    AA01.......01S......01S_005A.....16,49.......37... ....36.........PARK
    AA01.......01S......01S_006......34,03.......37... ....33.........PARK
    AA01.......01S......01S_006......34,03.......37... ....36.........PARK


    Now How can I write my view to get correct percentage??

    Thanks very much!

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    by this view:
    BL_ID.....FL_ID......RM_ID.....AREA........UL_ID.. ..UL_ID_SERV RM_USE
    AA01.......01S......01S_001 52,68.......34.......33.........PARK
    AA01.......01S......01S_003 7,15........34.......33.........PARK
    AA01.......01S......01S_004 14,32.......35.......33.........PARK
    AA01.......01S......01S_004 14,32.......35.......36.........PARK
    AA01.......01S......01S_005 18,76.......37.......33.........PARK
    AA01.......01S......01S_005 18,76.......37.......36.........PARK
    AA01.......01S......01S_005A 16,49.......37.......33.........PARK
    AA01.......01S......01S_005A 16,49.......37.......36.........PARK
    AA01.......01S......01S_006 34,03.......37.......33.........PARK
    AA01.......01S......01S_006 34,03.......37.......36.........PARK

    Is possible create this view?

    UL_ID........RM_USE.......AREA......PERCENT.....UL _ID_SERV
    34..........PARK.........7.15......4.99..........3 3
    34..........PARK.........52.68.....52.68.........3 3
    35..........PARK.........14.32.....9.98..........3 3
    37..........PARK.........18,76.....13.08.........3 3
    37..........PARK.........16.49.....11.50.........3 3
    37..........PARK.........34.03.....23.73.........3 3
    35..........PARK.........14.32.....17.13.........3 6
    37..........PARK.........18.76.....22.44.........3 6
    37..........PARK.........16.49.....19.72.........3 6
    37..........PARK.........34.03.....40.70.........3 6


    UL_ID_SERV=33 TOT_AREA=143.43
    7.15*100/143.43=4.99

    UL_ID_SERV=36 TOT_AREA=83.60
    14.32*100/83.60=17.13

    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
  •