Final energy consumption by transport modes

PREFIX sdmx-measure: <http://purl.org/linked-data/sdmx/2009/measure#> 
PREFIX sdmx-dimension: <http://purl.org/linked-data/sdmx/2009/dimension#>
PREFIX sdmx-attribute: <http://purl.org/linked-data/sdmx/2009/attribute#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX property: <http://rdfdata.eionet.europa.eu/eurostat/property#> 
PREFIX geo: <http://dd.eionet.europa.eu/vocabulary/eurostat/geo/>
PREFIX infotype: <http://rdfdata.eionet.europa.eu/eurostat/dic/infotype#>
PREFIX unit: <http://dd.eionet.europa.eu/vocabulary/eurostat/unit/>
PREFIX siec: <http://dd.eionet.europa.eu/vocabulary/eurostat/siec/>
PREFIX nrg_bal: <http://dd.eionet.europa.eu/vocabulary/eurostat/nrg_bal/>

SELECT
year(?date) as ?year 
round(sum(?INTMARB)/100000)/10.0  as ?International_Marine_Bunkers 
round(sum(?FC_TRA_RAIL_E)/100000)/10.0 as ?Rail_Transport 
round((sum(?FC_TRA_ROAD_E) + sum(?FC_TRA_ROAD_E_el*3.6))/100000)/10.0 as ?Road_Transport 
##round(sum(?INTAVI)/100000)/10.0 as ?International_Aviation 
##round(sum(?FC_TRA_DAVI_E)/100000)/10.0 as ?Domestic_Aviation 
round(( sum(?INTAVI)+sum(?FC_TRA_DAVI_E) )/100000)/10.0 as ?Domestic_International_Aviation 
round(sum(?FC_TRA_DNAVI_E)/100000)/10.0 as ?Domestic_Navigation
round(sum(?total)/100000)/10.0 as ?Total
round((sum(?road)+sum(?rail)+sum(?aviation)+sum(?ship))/100000)/10.0 as ?oil_derived_fuels
round((sum(?road)+sum(?rail)+sum(?aviation)+sum(?ship))/100000)/10.0*0.3 AS ?annotation_line_computed 


#use of the computed value for 2008 as constant for all the years 
xsd:double(5.2) as ?annotation_line

  WHERE {{{
    GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_bal_s.rdf.gz> { 
    {
        _:nrg_bal_s sdmx-attribute:unitMeasure unit:TJ .
        _:nrg_bal_s property:siec siec:TOTAL .
        _:nrg_bal_s sdmx-dimension:refArea ?geo . 
        _:nrg_bal_s sdmx-dimension:timePeriod ?date .
        { 
          _:nrg_bal_s property:nrg_bal nrg_bal:INTMARB .
	  _:nrg_bal_s sdmx-measure:obsValue ?INTMARB .
	} UNION { 
           _:nrg_bal_s property:nrg_bal nrg_bal:FC_TRA_RAIL_E .
	  _:nrg_bal_s sdmx-measure:obsValue ?FC_TRA_RAIL_E .
	} UNION { 
          _:nrg_bal_s property:nrg_bal nrg_bal:FC_TRA_ROAD_E .
	  _:nrg_bal_s sdmx-measure:obsValue ?FC_TRA_ROAD_E .
	} UNION { 
          _:nrg_bal_s property:nrg_bal nrg_bal:INTAVI .
	  _:nrg_bal_s sdmx-measure:obsValue ?INTAVI .
	} UNION { 
          _:nrg_bal_s property:nrg_bal nrg_bal:FC_TRA_DAVI_E .
	  _:nrg_bal_s sdmx-measure:obsValue ?FC_TRA_DAVI_E .
	} UNION { 
          _:nrg_bal_s property:nrg_bal nrg_bal:FC_TRA_DNAVI_E .
    	  _:nrg_bal_s sdmx-measure:obsValue ?FC_TRA_DNAVI_E .
	} UNION { 
        _:nrg_bal_s property:nrg_bal ?nrg_bal . 
        FILTER (?nrg_bal IN (nrg_bal:INTMARB, nrg_bal:FC_TRA_RAIL_E,
                nrg_bal:FC_TRA_ROAD_E, nrg_bal:FC_TRA_DAVI_E, 
                nrg_bal:INTAVI, nrg_bal:FC_TRA_DNAVI_E))
	_:nrg_bal_s sdmx-measure:obsValue ?total .
     }
    }}
  } UNION {
    GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_cb_oil.rdf.gz> { 
    {
    _:nrg_cb_oil sdmx-dimension:refArea ?geo .
    _:nrg_cb_oil sdmx-attribute:unitMeasure unit:THS_T .
    _:nrg_cb_oil sdmx-dimension:timePeriod ?date .
    { 
      _:nrg_cb_oil property:nrg_bal nrg_bal:FC_TRA_ROAD_E .
      {
        _:nrg_cb_oil property:siec ?siec . 
        FILTER (?siec in (siec:O4652XR5210B, siec:O4671XR5220B, siec:O4630))
        _:nrg_cb_oil sdmx-measure:obsValue ?road .
      }
    } UNION {
      _:nrg_cb_oil property:nrg_bal nrg_bal:FC_TRA_RAIL_E .
      {
        _:nrg_cb_oil property:siec ?siec . 
        FILTER (?siec in (siec:O4671XR5220B, siec:O4000))
        _:nrg_cb_oil sdmx-measure:obsValue ?rail .
      }
    } UNION {
      _:nrg_cb_oil property:nrg_bal ?nrg_bal . 
      FILTER (?nrg_bal IN (nrg_bal:INTAVI, nrg_bal:FC_TRA_DAVI_E))
      {
        _:nrg_cb_oil property:siec ?siec . 
        FILTER (?siec in (siec:O4651, siec:O4661XR5230B))
        _:nrg_cb_oil sdmx-measure:obsValue ?aviation .
      }
    }  UNION {
      _:nrg_cb_oil property:nrg_bal ?nrg_bal . 
      FILTER (?nrg_bal IN (nrg_bal:FC_TRA_DNAVI_E, nrg_bal:INTMARB))
      {
        _:nrg_cb_oil property:siec ?siec . 
        FILTER (?siec in (siec:O4671XR5220B, siec:O4000, siec:O4652XR5210B))
        _:nrg_cb_oil sdmx-measure:obsValue ?ship .
      }
    }
   }}
  } UNION {
     GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_cb_e.rdf.gz> { 
      {
        _:nrg_cb_e sdmx-dimension:refArea ?geo .

        _:nrg_cb_e sdmx-attribute:unitMeasure unit:GWH .
        _:nrg_cb_e sdmx-dimension:timePeriod ?date .
        _:nrg_cb_e property:nrg_bal nrg_bal:FC_TRA_ROAD_E .
        _:nrg_cb_e property:siec siec:E7000 .
        _:nrg_cb_e sdmx-measure:obsValue ?FC_TRA_ROAD_E_el .
      }
     }
  }} 
  FILTER (?geo in (geo:EU28))
}
GROUP BY ?date 
ORDER BY ?date