query-858f1cb67e565bb6901673899e4a7694

rq turtle/ttl

Andrew Gray

For a given UK parliament, find

- how many MPs sat in it

- how many of those have died

- how many of those who have died are in the ODNB

- how many overall are in the ODNB

(remember the ODNB only adds people three years after their death)

SELECT ?parliamentLabel ?start ?end ?mpcount ?odnbcount ?percent_in_odnb ?diedcount ?percent_died_in_odnb

WITH { # all MPs with their associated parliament SELECT DISTINCT ?mp ?parliament WHERE { ?mp wdt:P31 wd:Q5. # using the new data model - so limits how far back it goes ?mp p:P39/ps:P39 ?position . ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament. } } AS %MPsWithParliament

WITH { # count of ODNB entries in each parliament SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?odnb) WHERE { INCLUDE %MPsWithParliament. ?mp wdt:P1415 ?odnb } GROUP BY ?parliament } AS %odnb

WITH { # count of dead MPs in each parliament SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?died) WHERE { INCLUDE %MPsWithParliament. ?mp wdt:P570 ?died . } GROUP BY ?parliament } AS %died

WITH { # count of members entries in each parliament SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?mps) WHERE { ?mp wdt:P31 wd:Q5. ?mp p:P39/ps:P39 ?position. ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament. } GROUP BY ?parliament } AS %mpcount

WHERE { # get ?odnb INCLUDE %odnb INCLUDE %mpcount INCLUDE %died BIND(?odnb AS ?odnbcount) BIND(?mps AS ?mpcount) BIND(ROUND(100(?odnbcount/?mpcount)) AS ?percent_in_odnb) BIND(?died AS ?diedcount) BIND(ROUND(100(?odnbcount/?diedcount)) AS ?percent_died_in_odnb)

# get parliament dates ?parliament wdt:P571|wdt:P580 ?start. ?parliament wdt:P576|wdt:P582 ?end. # add labels SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } ORDER BY ?start

Use at

• https://query.wikidata.org/sparql

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
#Andrew Gray
# For a given UK parliament, find
# - how many MPs sat in it
# - how many of those have died
# - how many of those who have died are in the ODNB
# - how many *overall* are in the ODNB
#   (remember the ODNB only adds people three years after their death)
#

SELECT ?parliamentLabel ?start ?end ?mpcount ?odnbcount ?percent_in_odnb ?diedcount ?percent_died_in_odnb 









WHERE {
  # get ?odnb
   {
  # count of ODNB entries in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?odnb) WHERE {
     {
  # all MPs with their associated parliament
  SELECT DISTINCT ?mp ?parliament WHERE {
    ?mp wdt:P31 wd:Q5.
      # using the new data model - so limits how far back it goes
      ?mp p:P39/ps:P39 ?position . 
      ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament.
    }
}
    ?mp wdt:P1415 ?odnb
  }
  GROUP BY ?parliament
}   {
  # count of members entries in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?mps) WHERE {
    ?mp wdt:P31 wd:Q5.
      ?mp p:P39/ps:P39 ?position.
      ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament.
  }
  GROUP BY ?parliament
}   {
  # count of dead MPs in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?died) WHERE {
     {
  # all MPs with their associated parliament
  SELECT DISTINCT ?mp ?parliament WHERE {
    ?mp wdt:P31 wd:Q5.
      # using the new data model - so limits how far back it goes
      ?mp p:P39/ps:P39 ?position . 
      ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament.
    }
}
    ?mp wdt:P570 ?died . 
  }
  GROUP BY ?parliament
}  BIND(?odnb AS ?odnbcount)
  BIND(?mps AS ?mpcount)
  BIND(ROUND(100*(?odnbcount/?mpcount)) AS ?percent_in_odnb) 
  BIND(?died AS ?diedcount)
  BIND(ROUND(100*(?odnbcount/?diedcount)) AS ?percent_died_in_odnb) 

  # get parliament dates
  ?parliament wdt:P571|wdt:P580 ?start.
  ?parliament wdt:P576|wdt:P582 ?end.
  # add labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?start

Query found at

• https://www.wikidata.org/wiki/User:Charles_Matthews/Queries