query-b7335c823e42dc39b3d813131aa29cd7
UK Parliament constituencies and the number of MPs who died in office
analysis restricted to "since 1929"
SELECT DISTINCT ?constituency ?constituencyLabel ?startyear ?endyear ?died ?total ?percent_died_in_office
{
?constituency wdt:P31 wd:Q27971968 .
optional { ?constituency wdt:P571 ?start . bind(year(?start) as ?startyear)}
optional { ?constituency wdt:P576 ?end . bind(year(?end) as ?endyear)}
# find all constituencies
{ SELECT ?constituency (COUNT (distinct ?member) AS ?died) WHERE {
?constituency wdt:P31 wd:Q27971968 .
?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P1534 wd:Q5247364 .
?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) .
} GROUP BY ?constituency }
# find and count all people who died in office
{ SELECT ?constituency (COUNT (distinct ?member) AS ?total) WHERE {
?constituency wdt:P31 wd:Q27971968 .
?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency .
?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) .
} GROUP BY ?constituency }
# find and count all people elected
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
bind ((?died/?total)*100 as ?percent_died_in_office)
}
ORDER BY desc(?percent_died_in_office)
Use at
- https://query.wikidata.org/sparql
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
# UK Parliament constituencies and the number of MPs who died in office
# analysis restricted to "since 1929"
#
SELECT DISTINCT ?constituency ?constituencyLabel ?startyear ?endyear ?died ?total ?percent_died_in_office
{
?constituency wdt:P31 wd:Q27971968 .
optional { ?constituency wdt:P571 ?start . bind(year(?start) as ?startyear)}
optional { ?constituency wdt:P576 ?end . bind(year(?end) as ?endyear)}
# find all constituencies
{ SELECT ?constituency (COUNT (distinct ?member) AS ?died) WHERE {
?constituency wdt:P31 wd:Q27971968 .
?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P1534 wd:Q5247364 .
?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) .
} GROUP BY ?constituency }
# find and count all people who died in office
{ SELECT ?constituency (COUNT (distinct ?member) AS ?total) WHERE {
?constituency wdt:P31 wd:Q27971968 .
?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency .
?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) .
} GROUP BY ?constituency }
# find and count all people elected
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
bind ((?died/?total)*100 as ?percent_died_in_office)
}
ORDER BY desc(?percent_died_in_office)