|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Create one (or more) oracle view
Hi,
I've this table TEST_BL: BUILDING........FLOOR....COD_RIT......TYPE_S........AREA 00600569........P01................................O...................35,19 00600569........P01................................O...................1,91 00600569........P01................................O...................775,24 00600569........P01................................V...................223,36 00600569........P01................................V...................85,57 00600569........P01................................V...................49,86 00600569........P01................................V...................85,67 00600569........P01........05........................................25,28 00600569........P01........01........................................99,84 00600569........P01........01........................................760,94 00600569........P02................................O..................12,62 00600569........P02................................V...................212,03 00600569........P02................................V..................85,21 00600569........P02................................V..................49,44 00600569........P02........05........................................10,40 00600569........P02................................O..................326,94 00600569........P02................................V...................96,59 00600569........P02........01........................................96,23 00600569........P02........01........................................536,97 00600569........P03................................O...................50,49 00600569........P03................................V...................85,21 00600569........P03................................V...................14,87 00600569........P03........05........................................10,40 00600569........P03................................O.................539,00 00600569........P03................................V..................96,58 00600569........P03........07........................................96,82 00600569........P03........07.........................................259,26 I'd like to obtain this result: 00600569........01.......3730,28 00600569........05.......46,08 00600569........07.......945,57 The conditions in order to obtain this result are: For TYPE_S=O I must group by FLOOR For TYPE_S=V I must group by BUILDING For P01 01 SUM_AREA=760,94+99,84=860,78 05 SUM_AREA=25,28 MAX AREA BY COD_RIT=01 For P02 01 SUM_AREA=536,97+96,23=633,2 05 SUM_AREA=10,40 MAX AREA BY COD_RIT=01 For P03 07 SUM_AREA=259,26+96,82=356,08 05 SUM_AREA=10,40 MAX AREA BY COD_RIT=07 Now, all TYPE_S=O must have the COD_RIT with greater area. BUILDING........FLOOR....COD_RIT......TYPE_S........AREA 00600569........P01........01...........O...........35,19 00600569........P01........01...........O...........1,91 00600569........P01........01...........O...........775,24 00600569........P01.....................V...........223,36 00600569........P01.....................V...........85,57 00600569........P01.....................V...........49,86 00600569........P01.....................V...........85,67 00600569........P01........05.......................25,28 00600569........P01........01.......................99,84 00600569........P01........01.......................760,94 00600569........P02........01...........O...........12,62 00600569........P02.....................V...........212,03 00600569........P02.....................V...........85,21 00600569........P02.....................V...........49,44 00600569........P02........05.......................10,40 00600569........P02........01...........O...........326,94 00600569........P02.....................V...........96,59 00600569........P02........01.......................96,23 00600569........P02........01.......................536,97 00600569........P03........07...........O...........50,49 00600569........P03.....................V...........85,21 00600569........P03.....................V...........14,87 00600569........P03........05.......................10,40 00600569........P03........07...........O...........539,00 00600569........P03.....................V...........96,58 00600569........P03........07.......................96,82 00600569........P03........07.......................259,26 Sum area group by floor and cod_rit is: 00600569........P01........01.......................1673,12 00600569........P01.....................V...........223,36 00600569........P01.....................V...........85,57 00600569........P01.....................V...........49,86 00600569........P01.....................V...........85,67 00600569........P01........05.......................25,28 00600569........P02........01.......................972,77 00600569........P02.....................V...........212,03 00600569........P02.....................V...........85,21 00600569........P02.....................V...........49,44 00600569........P02........05.......................10,40 00600569........P02.....................V...........96,59 00600569........P03........07.......................945,57 00600569........P03.....................V...........85,21 00600569........P03.....................V...........14,87 00600569........P03........05.......................10,40 00600569........P03.....................V...........96,58 Now sum area by TYPE_S=V group by BUILDING: 00600569........01..............2645,89 (1673,12+972,77) 00600569................V.......1084,39 (444,46+443,27+196,66) 00600569........05..............46,08 (25,28+10,40+10,40) 00600569........07..............945,57 Finally, for TYPE_S=V it must include in the COD_RIT with greater area (in this case 01) 00600569........01..............3730,28 00600569........05..............46,08 00600569........07..............945,57 it is possible to create one (or more) view oracle to obtain this result, starting from my table TEST_BL?? thanks in advance!!! |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Create one (or more) oracle view |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|