hi,
I've table: "pulizie_pm"


COD_IMM.........PIANO........COD_RITMI.....TIPO_SP AZIO.......AREA_LOCALI
02015149.........P01.............................. .............V..................11,20
02015149.........P01............01................ ...............................4,08
02015149.........P01.............................. .............O..................3,78
02015149.........P01............06................ ...............................296,73
02015149.........PTE.............................. .............V..................18,30
02015149.........PTE............01................ ................................5,12
02015149.........PTE............04................ ...............................14,15
02015149.........PTE............05................ ...............................32,32
02015149.........PTE.............................. .............O..................14,99
02015149.........PTE.............................. .............O..................15,21
02015149.........PTE............05................ ................................60,26
02015149.........PTE............06................ .................................53,96
02015149.........S01.............................. ..............V..................8,74
02015149.........S01.............................. ..............O..................29,55
02015149.........S01.............................. ...............O..................68,23
02015149.........S01............05................ .................................72,19
02015149.........S01............06................ .................................33,70

view N.1

CREATE OR REPLACE VIEW TEST1 ( COD_IMM,
PIANO, COD_RITMI, CONTA_RITMI, TOTAL_AREA
) AS SELECT cod_imm, piano, cod_ritmi, COUNT(COD_RITMI),SUM(area_locali) total_area
FROM pulizie_pm
WHERE cod_ritmi IS NOT NULL
GROUP BY cod_imm, piano, cod_ritmi



COD_IMM.........PIANO......COD_RITMI......CONTA_RI TMI......TOTAL_AREA
02015149.........P01.........01................... .............1..............4,08
02015149.........P01.........06................... .............1..............296,73
02015149.........PTE.........01................... .............1..............5,12
02015149.........PTE.........04................... .............1..............14,15
02015149.........PTE.........05................... .............2..............92,58
02015149.........PTE.........06................... .............1..............53,96
02015149.........S01.........05................... .............1..............72,19
02015149.........S01.........06................... .............1..............33,7

VIEW N.2

CREATE OR REPLACE VIEW MAX_TEST1 ( COD_IMM,
PIANO, COD_RITMI, TOTAL_AREA, MAX_AREA
) AS SELECT "COD_IMM","PIANO","COD_RITMI","TOTAL_AREA","MAX_AREA"
FROM (SELECT a.*,
MAX(total_area) OVER (PARTITION BY cod_imm, piano) max_area
FROM test1 a)
WHERE max_area = total_area


COD_IMM..........PIANO......COD_RITMI......TOTAL_A REA......MAX_AREA
02015149..........P01..........06................. ...........296,73............296,73
02015149..........PTE..........05................. ............92,58............92,58
02015149..........S01..........05................. ............72,19............72,19

VIEW N.3

CREATE OR REPLACE VIEW MAX_TEST1CONTA ( COD_IMM,
COD_RITMI, MAX_RITMI, TOTAL_AREA ) AS SELECT COD_IMM,COD_RITMI, sum(conta_ritmi),SUM(TOTAL_AREA)
from test1
group by COD_IMM,COD_RITMI



COD_IMM.........COD_RITMI.......MAX_RITMI.......TO TAL_AREA
02015149.........01..............................2 .................9,2
02015149.........04............................... 1.................14,15
02015149.........05............................... 3.................164,77
02015149.........06............................... 3.................384,39

VIEW N.4

CREATE OR REPLACE VIEW COD_WITH_MAX_COD_RITMI ( COD_IMM,
PIANO, COD_RITMI, TIPO_SPAZIO, AREA_LOCALI
) AS SELECT bl.cod_imm,
bl.piano,
(CASE WHEN tipo_spazio = 'O'
THEN max.cod_ritmi
ELSE bl.cod_ritmi
END) cod_ritmi,
bl.tipo_spazio,
bl.area_locali
FROM pulizie_pm bl, max_test1 max
WHERE bl.cod_imm(+) = max.cod_imm
AND bl.piano(+) = max.piano

COD_IMM..........PIANO.......COD_RITMI.......TIPO_ SPAZIO..........AREA_LOCALI
02015149..........PTE............................. ...................V....................18,3
02015149..........PTE..........01................. ........................................5,12
02015149..........PTE..........06................. ........................................53,96
02015149..........PTE..........05................. ..................O....................15,21
02015149..........PTE..........05................. ........................................60,26
02015149..........PTE..........04................. ........................................14,15
02015149..........PTE..........05................. ...................O....................14,99
02015149..........PTE..........05................. ..........................................32,32
02015149..........P01............................. ......................V....................11,2
02015149..........P01..........01................. ...........................................4,08
02015149..........P01..........06................. ....................O....................3,78
02015149..........P01..........06................. ...........................................296,73
02015149..........S01............................. .....................V....................8,74
02015149..........S01..........05................. ....................O....................29,55
02015149..........S01..........06................. ..........................................33,7
02015149..........S01..........05................. .........................................72,19
02015149..........S01..........05................. ...................O....................68,23

VIEW N.5

CREATE OR REPLACE VIEW TEST4 ( COD_IMM,
COD_RITMI, TIPO_SPAZIO, TOTAL_AREA ) AS (SELECT cod_imm,
cod_ritmi,
null tipo_spazio,
SUM(area_locali) total_area
FROM cod_with_max_cod_ritmi
WHERE tipo_spazio = 'O'
OR tipo_spazio IS NULL
GROUP BY cod_imm, cod_ritmi)
UNION ALL
(SELECT cod_imm,
Null cod_ritmi,
'V' tipo_spazio,
SUM(area_locali) total_area
FROM cod_with_max_cod_ritmi
WHERE tipo_spazio = 'V'
GROUP BY cod_imm)

COD_IMM.........COD_RITMI.........TIPO_SPAZIO..... .......TOTAL_AREA
02015149.........01............................... ...................................9,2
02015149.........04............................... ..................................14,15
02015149.........05............................... ..................................292,75
02015149.........06............................... ...................................388,17
02015149.......................................... ..V..................................38,24


Now I must create a view TO INSERT "TIPO_SPZIO=V" in COD_RITMI with greater
frequency (max(max_ritmi)), but if the max it coincides it is necessary to take the area greater.

In this case from view MAX_TEST1CONTA I get:
cod_ritmi=05 max=3
cod_ritmi=06 max=3

I tried this view:
CREATE OR REPLACE VIEW TEST_FINALCONTA ( COD_IMM,
COD_RITMI, SOMMA ) AS select cod_imm,
cod_ritmi,
SUM(total_area) somma
from (select cod_imm,
(CASE WHEN cod_ritmi IS NOT NULL
THEN cod_ritmi
ELSE (SELECT cod_ritmi
FROM MAX_TEST1CONTA
WHERE max_ritmi = (SELECT MAX(max_ritmi)
FROM MAX_TEST1CONTA) )
END) cod_ritmi,
tipo_spazio,
total_area
from test4)
group by cod_imm, cod_ritmi


I get error: ORA-01427 because max(max_ritmi) has 2 records.
If max(max_ritmi) has more one records I must take the area greater, in this case cod_ritmi=06
and i'll have this result final:

COD_IMM.........COD_RITMI.........TIPO_SPAZIO..... .......TOTAL_AREA
02015149.........01............................... ................................9,2
02015149.........04............................... ..............................14,15
02015149.........05............................... ...............................292,75
02015149.........06............................... ...........................388,17+38,24=426,41

Thanks in advance!!!!