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

[raf]

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

[ales]

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

[raf]

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

[ales]

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