Transport energy consumption

PREFIX sdmx-measure: <http://purl.org/linked-data/sdmx/2009/measure#> 
PREFIX sdmx-dimension: <http://purl.org/linked-data/sdmx/2009/dimension#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX property: <http://rdfdata.eionet.europa.eu/eurostat/property#> 
PREFIX c: <http://rdfdata.eionet.europa.eu/eurostat/dic/coicop#> 
PREFIX g: <http://rdfdata.eionet.europa.eu/eurostat/dic/geo#>
PREFIX i: <http://rdfdata.eionet.europa.eu/eurostat/dic/infotype#>
PREFIX u: <http://rdfdata.eionet.europa.eu/eurostat/dic/unit#>
PREFIX p: <http://rdfdata.eionet.europa.eu/eurostat/dic/product#>
PREFIX indic_nrg: <http://rdfdata.eionet.europa.eu/eurostat/dic/indic_nrg#>


SELECT 
year(?date) as ?year

sum(?road_gasoline) as ?road_gasoline
sum(?road_diesel) as ?road_diesel

(sum(?rail_gas_diesel_oil)+sum(?rail_residual_fuel_oil)+sum(?rail_electricity)) as ?rail_gas_diesel_residual_fuel_electricity

sum(?aviation_kerosene) as ?aviation_kerosene

(sum(?ship_diesel_gas_oil)+sum(?ship_residual_fuel_oil)+sum(?ship_gasoline)) as ?ship_diesel_residual_fuel_gasoline

(sum(?biogasoline)+sum(?biodiesel)+sum(?natural_gas)+sum(?lpg)) as ?biogasoline_biodiesel_natural_gas_lpg

(sum(?road_gasoline)+sum(?road_diesel)+sum(?rail_gas_diesel_oil)+sum(?rail_residual_fuel_oil)+sum(?aviation_gasoline)+sum(?aviation_kerosene)+sum(?ship_diesel_gas_oil)+sum(?ship_residual_fuel_oil)+sum(?ship_gasoline)+sum(?lpg)) as ?oil_derived_fuels

(sum(?road_gasoline)+sum(?road_diesel)+sum(?rail_gas_diesel_oil)+sum(?rail_residual_fuel_oil)+sum(?aviation_gasoline)+sum(?aviation_kerosene)+sum(?ship_diesel_gas_oil)+sum(?ship_residual_fuel_oil)+sum(?ship_gasoline)+sum(?lpg))*0.3 AS ?annotation_line_computed

#use of the computed value for 2009 as constant for all the years
5312301 as ?annotation_line

WHERE {
{
  GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_102a.rdf.gz> 
    {
    _:nrg_102a property:geo ?geo .
    _:nrg_102a property:unit u:TJ .
    _:nrg_102a sdmx-dimension:timePeriod ?date .
    { 
      _:nrg_102a property:indic_nrg indic_nrg:B_101920 .
      {
      _:nrg_102a property:product p:3234 .
      _:nrg_102a sdmx-measure:obsValue ?road_gasoline .
      } UNION { 
      _:nrg_102a property:product p:3260 .
      _:nrg_102a sdmx-measure:obsValue ?road_diesel .
      } UNION {
      _:nrg_102a property:product p:3220 .
      _:nrg_102a sdmx-measure:obsValue ?lpg .
      }
    } UNION {
      _:nrg_102a property:indic_nrg indic_nrg:B_101910 .
      {
      _:nrg_102a property:product p:3260 .
      _:nrg_102a sdmx-measure:obsValue ?rail_gas_diesel_oil .
      } UNION {
      _:nrg_102a property:product p:3270A .
      _:nrg_102a sdmx-measure:obsValue ?rail_residual_fuel_oil .
      }
    } UNION {
      _:nrg_102a property:indic_nrg ?indic_nrg FILTER (?indic_nrg IN (indic_nrg:B_101931,indic_nrg:B_101932)) .
      {
      _:nrg_102a property:product p:3235 .
      _:nrg_102a sdmx-measure:obsValue ?aviation_gasoline .
      } UNION {
      _:nrg_102a property:product p:3240 .
      _:nrg_102a sdmx-measure:obsValue ?aviation_kerosene .
      }
    }  UNION {
      _:nrg_102a property:indic_nrg ?indic_nrg FILTER (?indic_nrg IN (indic_nrg:B_101940,indic_nrg:B_100800)) .
      {
      _:nrg_102a property:product p:3260 .
      _:nrg_102a sdmx-measure:obsValue ?ship_diesel_gas_oil .
      } UNION {
      _:nrg_102a property:product p:3270A .
      _:nrg_102a sdmx-measure:obsValue ?ship_residual_fuel_oil .
      } UNION {
      _:nrg_102a property:product p:3234 .
      _:nrg_102a sdmx-measure:obsValue ?ship_gasoline .
      }
    } 
  }
  } UNION {
  GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_105a.rdf.gz> 
    {
    _:nrg_105a property:geo ?geo .
    _:nrg_105a sdmx-dimension:timePeriod ?date .
    _:nrg_105a property:unit u:TJ .    
    _:nrg_105a property:indic_nrg indic_nrg:B_101910 .
    _:nrg_105a property:product p:6000 .
    _:nrg_105a sdmx-measure:obsValue ?rail_electricity .
    }
  } UNION {
  GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_1073a.rdf.gz> 
    {
    _:nrg_1073a property:geo ?geo .
    _:nrg_1073a sdmx-dimension:timePeriod ?date .
    _:nrg_1073a property:unit u:TJ . 
    _:nrg_1073a property:indic_nrg indic_nrg:B_101900 .   
    { 
      _:nrg_1073a property:product p:5546 .
      _:nrg_1073a sdmx-measure:obsValue ?biogasoline .
    } UNION {
      _:nrg_1073a property:product p:5547 .
      _:nrg_1073a sdmx-measure:obsValue ?biodiesel .
    }
  } 
  } UNION {
  GRAPH <http://rdfdata.eionet.europa.eu/eurostat/data/nrg_103a.rdf.gz>
    {
    _:nrg_103a property:geo ?geo .
    _:nrg_103a sdmx-dimension:timePeriod ?date .
    _:nrg_103a property:unit u:TJ . 
    _:nrg_103a property:indic_nrg indic_nrg:B_101920 .   
    _:nrg_103a property:product p:4100 .
    _:nrg_103a sdmx-measure:obsValue ?natural_gas .
    } 
  
 } 
 ?geo skos:notation ?ugeo FILTER (?ugeo != 'IS' && ?ugeo != 'LI') .
 ?eeageo owl:sameAs ?geo . 
 ?eeageo skos:inScheme <http://rdfdata.eionet.europa.eu/eea/countries/> . 
 ?memberOf skos:member ?eeageo . 
 ?memberOf skos:notation ?memberOfcode FILTER (?memberOfcode IN ('EEA32')) .
} GROUP BY YEAR(?date)
  ORDER BY ?date