create view to calculate values........
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: create view to calculate values........

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    create view to calculate values........

    I have table TAB_USE:

    ID.....USE.......UL_ID.......AREA_POND
    1......ALG.........41...........20,1
    3......ALG.........41...........26
    5......ALG.........42...........23
    7......ALG.........42...........10,1
    8......ALG.........42...........50
    10.....ALG.........43...........49,31
    11.....AE..........75...........10,1
    13.....AE..........75...........300
    15.....AE..........75...........300
    16.....AE..........76...........50
    18.....AE..........76...........20
    19.....AE..........77...........10,5
    20.....AE..........83...........7,5
    21.....AE..........78...........30
    24.....AE..........78...........353.9
    27....COMM.........7............5,18
    28....COMM.........8............5,00
    29....COMM.........9............20
    30....COMM.........12...........300
    33.....CP..........62...........100
    33.....CP..........68...........16,19
    34....INF..........1............134,44
    35....INF..........10...........200
    36....INF..........34...........400
    39....SP...........31...........153,3
    40....SP...........31...........100
    41....TP...........69...........45,34
    42....TP...........70...........300
    43....TRA..........18...........28,58
    44....TRA..........21...........1,00
    45....TRA..........38...........300

    SELECT SUM(AREA_POND) FROM TAB_USE = 3370

    If I wanted to calculate the percentage (with area_tot=3370) I would have to create this view.

    CREATE OR REPLACE VIEW PERC_TAB_USE (ID,
    USE, UL_ID, AREA_POND, PERCENT
    ) AS select ID, USE, UL_ID, AREA_POND, ROUND(AREA/AREA_TOT*100,2) PERCENT
    from
    ( select ID, USE, UL_ID, AREA_POND,sum(AREA_POND) AREA
    from TAB_USE
    GROUP BY ID, USE, UL_ID, AREA_POND
    ) a,
    ( select sum(AREA_POND) AREA_TOT
    from TAB_USE
    ) b

    ID=1 AREA_POND=20,1 PERCENT=(20,1/3370*100)=0,596%

    But my problem is create a view with calculation base not 3370 but 3370+1826,01 where 1826,01=sum(area_pond) by USE IN

    ('ALG','COMM','TRA', 'SP', 'INF').

    Therefore 3370+178,5+330,18+329,58+253,3+734,45=5916,01

    ID=1 AREA_POND=20,1 PERCENT=(20,1/5916,01*100) = 0,34%

    I tried to create a view by TAB_USE:

    CREATE OR REPLACE VIEW TAB_USE_VIEW (ID,
    USE, UL_ID, AREA_POND
    ) AS select ID, USE, UL_ID, AREA_POND
    from TAB_USE
    where USE IN ('ALG', 'COMM', 'TRA')
    UNION
    select ID, USE, UL_ID, AREA_POND
    WHERE USE IN ('INF')
    UNION
    select ID, USE, UL_ID, AREA_POND
    WHERE USE IN ('SP')


    SELECT SUM(AREA_POND) FROM TAB_USE_VIEW = 1826,01

    Now I don't Know How can I Join TAB_USE and TAB_USE_VIEW to calculate correct percentage.

    Is possibile calculate this percentage with a view?
    How can I resolve this problem?

    Thanks in advance!
    Raf

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    if I understand it well ...

    CREATE OR REPLACE VIEW PERC_TAB_USE (ID,
    USE, UL_ID, AREA_POND, PERCENT
    ) AS select ID, USE, UL_ID, AREA_POND, ROUND(AREA/(AREA_TOT+AREA_TOT2)*100,2) PERCENT
    from
    ( select ID, USE, UL_ID, AREA_POND,sum(AREA_POND) AREA
    from TAB_USE
    GROUP BY ID, USE, UL_ID, AREA_POND
    ) a,
    ( select sum(AREA_POND) AREA_TOT
    from TAB_USE
    ) b,
    ( select sum(AREA_POND) AREA_TOT2
    from TAB_USE
    where USE IN ('ALG','COMM','TRA', 'SP', 'INF')
    ) c
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    ok, thanks!
    but if I have this table:

    STATION.........ID.....USE.......UL_ID.......AREA_POND
    BA..............1......ALG.........41...........20,1
    BA..............3......ALG.........41...........26
    BA..............5......ALG.........42...........23
    BA..............7......ALG.........42...........10,1
    BA..............8......ALG.........42...........50
    BA..............10.....ALG.........43...........49,31
    BA..............11.....AE..........75...........10,1
    BA..............13.....AE..........75...........300
    BA..............15.....AE..........75...........300
    BA..............16.....AE..........76...........50
    ..................................................
    ..................................................
    RG..............1......ALG.........21...........12
    RG..............3......ALG.........21...........15,2
    RG..............5......ALG.........32...........22
    RG..............7......ALG.........32...........13,1
    RG..............8......ALG.........32...........50
    RG..............10.....ALG.........33...........48,31
    RG..............11.....AE..........65...........19,1
    RG..............13.....AE..........65...........390
    ..................................................
    ..................................................
    MI..............1......ALG.........11...........12
    MI..............3......ALG.........11...........15,2
    MI..............5......ALG.........12...........22
    MI..............7......ALG.........12...........13,1
    MI..............8......ALG.........12...........50
    MI..............10.....ALG.........93...........48,31
    MI..............11.....AE..........85...........19,1
    MI..............13.....AE..........85...........390
    ..................................................
    ..................................................
    ..................................................
    ..................................................

    Can I create just one view group by STATION?
    or must I create one view for each STATION?

    Thank you very much!
    Rgds
    Raf

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Code:
    create or replace view PERC_TAB_USE (STATION, ID, USE, UL_ID, AREA_POND, PERCENT)
    as
    select a.STATION, ID, USE, UL_ID, AREA_POND, ROUND(AREA/(b.AREA_TOT+c.AREA_TOT2)*100,2) PERCENT
    from
    (
      select STATION, ID, USE, UL_ID, AREA_POND,sum(AREA_POND) AREA
      from TAB_USE
      GROUP BY STATION, ID, USE, UL_ID, AREA_POND
    ) a,
    (
      select STATION, sum(AREA_POND) AREA_TOT
      from TAB_USE
      group by STATION
    ) b,
    (
      select STATION, sum(AREA_POND) AREA_TOT2
      from TAB_USE
      where USE IN ('ALG','COMM','TRA', 'SP', 'INF')
      group by STATION
    ) c
    where a.station=b.station
    and a.station=c.station
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

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



Click Here to Expand Forum to Full Width