-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|