Total aquaculture production of finfish, shellfish and aquatic plants

PREFIX tableFile: <http://semantic.eea.europa.eu/project/CSI033/FishStatUnpivot2019.tsv#>
PREFIX countrygroups: <http://dd.eionet.europa.eu/vocabulary/common/countrygroups/>
PREFIX countries: <http://dd.eionet.europa.eu/vocabulary/common/countries/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>


SELECT DISTINCT
?year 
?geoarea
?environment
?group
round(sum(xsd:double(?obsValue))/10.0)/100.0 as ?obsValue

{{
  SELECT DISTINCT
    xsd:integer(?year) as ?year
    ?geoarea
    ?group
    ?obsValue

    bif:either(?group != 'PISCES', '', 
           if(?environment = 'Brackishwater', 'Marine', ?environment) ) as ?environment

  WHERE {
    _:rec tableFile:iso2 ?iso2 .
    _:rec tableFile:speciesGroup ?speciesGroup .
    _:rec tableFile:species ?species .
    _:rec tableFile:group ?group .
    _:rec tableFile:area ?area .
    _:rec tableFile:environment ?environment .
    _:rec tableFile:unit ?unit .
    _:rec tableFile:year ?year .
    {
      _:rec tableFile:continent ?geoarea .
      FILTER (?geoarea != 'Europe')
      _:rec tableFile:obsValue ?obsValue .
    } UNION {
      ?member skos:inScheme countries: .
      ?member skos:notation ?iso2 .
      ?geoarea_uri skos:member ?member.
      ?geoarea_uri ?o countrygroups: .
      ?geoarea_uri skos:notation ?geoarea .
      _:rec tableFile:obsValue ?obsValue .
      FILTER (?geoarea_uri in (countrygroups:EU28 ))
    } UNION {
      { SELECT 'EEA39' as ?geoarea {} } .
      ?member skos:inScheme countries: .
      ?member skos:notation ?iso2 .
      ?geoarea_uri skos:member ?member.
      ?geoarea_uri ?o countrygroups: .
      _:rec tableFile:obsValue ?obsValue .
      FILTER (?geoarea_uri in (countrygroups:EEA33) || 
              ?member in (countries:AL, countries:BA, countries:MK, 
              countries:ME, countries:RS, countries:XK, countries:CS))
    }
  FILTER (xsd:integer(?year) > 1992)
  FILTER (?group IN ('MOLLUSCA', 'PISCES', 'PLANTAE AQUATICAE'))
  #FILTER (?group IN ('PISCES'))
  #FILTER (?geoarea IN ('EU28'))
  }
}}

GROUP BY ?year ?geoarea ?environment ?group
ORDER BY ?year ?geoarea ?environment