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

1. raf
Member
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?

Raf

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

3. raf
Member
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

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

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