Share of CDDA sites in major ecosystems by IUCN NA categories

PREFIX schema: <http://reference.eionet.europa.eu/cdda/schema/>

SELECT 
?major_ecosystem_type
?iucncat
?share
?sites
?site_area_total
1 as ?order

{
  SELECT
    'Ecosystem type not available' as ?major_ecosystem_type

    bif:either(UCASE(?iucncat) in ('UA', 'NA', 'NOTREPORTED'), 'Not reported', 
          bif:either(UCASE(?iucncat) in ('NOTAPPLICABLE'), 'Not applicable',
          bif:either(UCASE(?iucncat) in ('NOTASSIGNED'), 'Not assigned',
          REPLACE(STR(?iucncat),'A','a')))) as ?iucncat #'IA' -> 'Ia'

    sum(?site_area) as ?sites
    ?site_area_total
    round((sum(?site_area)/xsd:double(?site_area_total))*10000)/100.0 as ?share

  WHERE {
    GRAPH <http://r.eionet.europa.eu/rdfdumps/cdda/cddasites.rdf.gz> 
    {
      ?sites schema:site_code ?site_code .
      ?sites schema:site_area ?site_area .
      ?sites schema:iucncat ?iucncat .
      OPTIONAL { ?sites schema:major_ecosystem_type ?major_ecosystem_type } .
      FILTER (!bound(?major_ecosystem_type)) .
      { 
        SELECT DISTINCT
          sum(xsd:double(?site_area)) as ?site_area_total
        WHERE {
        GRAPH <http://r.eionet.europa.eu/rdfdumps/cdda/cddasites.rdf.gz> 
          {
            ?sites schema:site_area ?site_area .
            OPTIONAL { ?sites schema:major_ecosystem_type ?major_ecosystem_type } .
            FILTER (!bound(?major_ecosystem_type)) .
          }  
        }
      }
    }
  }
}
ORDER BY ?order ?iucncat