oracle spatial compute area of polgons inside a class of polygon group by polygon ID

Question

I have a table pline_sp, that have a geom column containing SDO_GEOMETRY polygons. Each polygon have a class type. I wrote a SQL query to find class C1 and find all polygons from class C2 that resides inside of class C1 and then sum the area of all.

I'm not sure this is the best way but query is as follows:

select sum(SDO_GEOM.SDO_AREA(result.geom, 0.005, 'unit=SQ_KM'))  
from (SELECT a.geom
      FROM pline_sp a,
           (select A.Geom from pline_sp A where A.class='C1') B
      WHERE SDO_RELATE(A.Geom, B.Geom, 'mask=inside') = 'TRUE' 
        AND A.Class='C2') result;

There are about 7 polygons from class C1 in table, each having different label.

I want to get sum of polygons area inside it separated by label not sum of all.

I want the result to be like this:

Label   Area
--------------
l324     321.54
l543     325.21

...

Does it have any solution in SQL or I should write procedure? Thanks.

Answer 1

I found the answer of my question. First the SQL could be more readable as follows and by adding S.label in select and GROUP BY S.label at the end everything will be OK:

 SELECT S.label,SUM(SDO_GEOM.SDO_AREA(M.geom,0.005,'unit=SQ_KM')) 
      from 
        (SELECT P.label,P.geom from pline_sp P WHERE P.class='class1') S,
         (SELECT P.geom from pline_sp P WHERE P.class='class2') M
         WHERE 
         SDO_RELATE(M.geom,S.geom,'mask=inside')='TRUE'
         GROUP BY S.label;