Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

Request a query
Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter.

This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2018/09.

Project
chat

Lexicographical
data

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Request
a query

Requests
for deletions

Requests
for comment

Bot
requests

Requests
for permissions

Property
proposal

Properties
for deletion

Partnerships
and imports

Interwiki
conflicts

Bureaucrats'
noticeboard

Slide show with images[edit]

I am trying to make a slideshow with automatic looping over the a set of images returned from a WDQS query. I have made some progress on a page that loops via CCS and Javascript. I was wondering if there is a simpler way. For instance, with SAMPLE and HTML meta refresh. This will not work:

#defaultView:ImageGrid
SELECT (SAMPLE(DISTINCT ?image) AS ?image) WHERE {
  ?image ^wdt:P18 / wdt:P50 / (wdt:P108| wdt:P463 | wdt:P1416/wdt:P361*) wd:Q24283660 .
}

Try it!

Query with timeout[edit]

Hello, could anyone help me work out this query ?(list the tunnels in the world by lenght)

SELECT ?tunnel ?tunnelLabel ?natureLabel ?longueur WHERE {
  ?tunnel (wdt:P31/wdt:P279*)  wd:Q44377.
  OPTIONAL { ?tunnel wdt:P2043 ?longueur. }
  OPTIONAL { ?tunnel wdt:P31 ?nature. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
order by desc(?longueur)
LIMIT 150

Try it! Bouzinac (talk) 07:12, 6 September 2018 (UTC)

@Bouzinac: fixed above. Changed ?item to ?tunnel in OPTIONAL { ?tunnel wdt:P31 ?nature. }--Tagishsimon (talk) 07:15, 6 September 2018 (UTC)

thanks!Bouzinac (talk) 07:28, 6 September 2018 (UTC)

@Bouzinac: Note that the values for length (P2043) can be given with different units that can not be compared/sorted without conversion to some standard unit for length. Gotthard Base Tunnel (Q193002) will not appear on the "top 150 list" since the value is "just" 57.09, but the unit is kilometer. Other tunnels have the units kilometre (Q828224), metre (Q11573), mile (Q253276), yard (Q482798) or foot (Q3710). In order to get a standard unit, meter, you can use the prefix psn:.
The following query has both psv and psn to show the difference.
SELECT DISTINCT ?tunnel ?tunnelLabel ?natureLabel ?longueur_psv ?unit_psvLabel ?longueur_psn ?unit_psnLabel WHERE {
  ?tunnel (wdt:P31/wdt:P279*)  wd:Q44377.
  OPTIONAL { ?tunnel p:P2043/psv:P2043 ?longueurnode_psv . 
             ?longueurnode_psv wikibase:quantityAmount ?longueur_psv .
             ?longueurnode_psv wikibase:quantityUnit ?unit_psv .
  } 
  OPTIONAL { ?tunnel p:P2043/psn:P2043 ?longueurnode_psn . 
             ?longueurnode_psn wikibase:quantityAmount ?longueur_psn .
             ?longueurnode_psn wikibase:quantityUnit ?unit_psn .
  }
  FILTER(BOUND(?longueur_psn))
  OPTIONAL { ?tunnel wdt:P31 ?nature. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?longueur_psn)
Try it!
--Larske (talk) 08:18, 6 September 2018 (UTC)
Touché!Bouzinac (talk) 11:47, 6 September 2018 (UTC)

Biographies about African-Americans[edit]

I'm new to Wiki data, but preparing for a class I'll be teaching. I realize this may be a tricky query, and I've tried it myself with the Query Helper, but I am having difficulty. Is there a way to figure out how many Biographies of African-Americans / Black Americans exist in Wikipedia? Specifically English Wikipedia.

Thank you for your help!

@bridges2information: Something like this could help you:
PREFIX schema: <http://schema.org/>
SELECT ?person ?personLabel ?article ?dateofBirth ?dateofDeath WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P172 wd:Q49085.
  ?article schema:about ?person.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?person wdt:P569 ?dateofBirth. }
  OPTIONAL { ?person wdt:P570 ?dateofDeath. }
}
Try it!
or if you want to have a number of biographic articles in English Wikipedia (Q328) try
PREFIX schema: <http://schema.org/>
SELECT (COUNT( DISTINCT ?person) AS ?count) WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P172 wd:Q49085.
  ?article schema:about ?person.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! Mfchris84 (talk) 05:14, 7 September 2018 (UTC)
Worth noting that ethnic group (P172) has an EN description saying "subject's ethnicity (consensus is that a VERY high standard of proof is needed for this field to be used. In general this means 1) the subject claims it him/herself, or 2) it is widely agreed on by scholars, or 3) is fictional and portrayed as such)" and so it's probable that measurements based on P172 will significantly under-count; but agree that Mfchris84's query is as good as it gets. --Tagishsimon (talk) 11:27, 9 September 2018 (UTC)

List of Catholics saints by date of festing[edit]

Hello, is there a way to show a table with calendar showing the saint of the day January, 1, (the saint) // January, 2, (the saint) etc ?

@Bouzinac: There are a number of issues with such a report. First, I suspect a lack of data for many saints - either that they do not have a canonization status (P411) value of Catholic saint (Q3464126), or they do not have a feast day (P841) statement. Second, feast day (P841) takes wikidata QIds as values (e.g. September 23 (Q2888)) which makes ordering the results into month order and day order challenging (and right now I've ducked said challenge). Then, the values for feast day (P841) are unlikely solely to be the Catholic feast days for the saint, but may be some other religion's feast days for them, and I have yet to find (or look hard for) qualifiers for the P841 which indicate from which calendar the date is taken. So, lots of room for improvement. A starter query is:
SELECT ?item ?itemLabel ?feastday ?feastdayLabel
WHERE {
  ?item wdt:P841 ?feastday . # has a feast day
  ?item wdt:P411 wd:Q3464126 .  # is a Catholic saint
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?feastdayLabel
Try it! --Tagishsimon (talk) 11:22, 9 September 2018 (UTC)

Country and city attribution of geolocated item[edit]

I am trying to make use of GeoData API to perform aforementioned task.

I found GeoData API very clean, simple and user friendly in retrieving various data according to geo location of the item. But there are difficulties with retrieving country/city affiliation of the item. While country can theoretically be get in a single request (also not always but only if being specified and not in name format but rather by its alphabetic designation), the city is possible to be get only for items which are cities by themselves. Let's imagine I want to determine in one request providing the coordinates of the Sagrada Familia temple the name of the item and that it is located in Barcelona, Spain. As far as I understood there is no way to do that. From the second hand this information does exist for every geo tagged item and is available for example through WikiData SPARQL query service. But then I'll need to perform a second request to WikiData which I would have liked to avoid by all means.

Looking on advice on the optimal strategy to perform the desired. If not is it possible to add the country/city attributes to GeoData list=geosearch attributes?

Comparing items by the coordinates[edit]

Hi!

Could someone make a map, showing items with coordinates from only ceb+sv WP and all the other/only one WP, labeling them in two colours to compare. Any type of items or borders of map welcome. Thanks in advance! - Kareyac (talk) 15:43, 9 September 2018 (UTC)

This query was done by Larske (talkcontribslogs) for Swedish civil parish code/ATA code (P777)
#defaultView:Map
SELECT ?item ?itemLabel ?coord1 ?layer WHERE
{
 { ?item wdt:P777 ?sockenkod .
  ?item p:P625 ?coord1stm . ?coord1stm prov:wasDerivedFrom ?ref1 . ?ref1 pr:P143 ?imp1 . ?coord1stm ps:P625 ?coord1 .
  ?item p:P625 ?coord2stm . ?coord2stm ps:P625 ?coord2 .  
  FILTER(?coord1<?coord2) 
  } UNION
  { ?item wdt:P777 ?sockenkod .
  ?item p:P625 ?coord1stm . ?coord1stm prov:wasDerivedFrom ?ref1 . ?ref1 pr:P143 ?imp1 . ?coord1stm ps:P625 ?coord1 .
  ?item p:P625 ?coord2stm . ?coord2stm ps:P625 ?coord2 .  
  FILTER(?coord1>?coord2)
  } 
  ?imp1 rdfs:label ?lab1 . FILTER(lang(?lab1)='sv')
  BIND(str(?lab1) AS ?layer) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv". }
}
ORDER BY ?itemLabel
Try it! - Salgo60 (talk) 18:38, 9 September 2018 (UTC)

@Salgo60:, @Larske:

Wow! Very interesting map! Thank you both.

It's not the query I failed to ask, this one shows items with two coordinates. It also works good with many other Wikidata property for authority control for places (Q19829908) (oops, fails with GeoNames ID (P1566) due to timeout limit) and gives good selection of places, where coordinates should be checked.

I need map of items (ex. geographical object (Q618123) or mountain (Q8502)) in the territory (ex. Armenia (Q399)) with articles present in hyWP, but absent in cebWP in colour A, united with map of the same type of geographical object (Q618123) in the same territory with articles present in cebWP, but absent in hyWP in colour B. Items with articles in both WPs better be excluded. This comparison will give possibility to find quickly items of the same type, that could be the same, but still not merged.

I also happen to list certain objects (any * of airports) for a specified country, ordered by coordinates, that helps merge duplicates. There are often orphans items in pl, ms and ceb wikipedias Bouzinac (talk) 07:24, 12 September 2018 (UTC)

Age of actors distributed by gender[edit]

Hello, i have written the following query which gives me the age actors had when they played in a movie, distributed by age :

SELECT ?genreLabel ?age (COUNT(*) as ?NB) WHERE {
      ?actor wdt:P569 ?dob .
      ?actor wdt:P21 ?genre .
      ?actor wdt:P106 wd:Q33999 .
      # ?movie wdt:P495 wd:Q142 .
      ?actor wdt:P27 wd:Q142 .
      ?movie wdt:P161 ?actor .
      ?movie wdt:P577 ?dateMovie .
      BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) . 
      FILTER(?age > 0) .
      FILTER(?age < 100) .
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
     }
  GROUP BY ?genreLabel ?age
  ORDER BY ?age

Try it!

That works fine, but for things to be easier to manipulate afterwards, I'd like to get the result as :

Age male (Q6581097) female (Q6581072)
1 15 13
2 10 30
3 37 16

and so on. To make things easier, I will limit to male (Q6581097) & female (Q6581072) for sex or gender (P21). Is there any way to do that withing SPARQL or do I need to process my data afterwards? Thanks, Symac (talk) 04:44, 11 September 2018 (UTC)

@Symac: Like this. There's also an issue of date precision to consider, should you want accurate results; a DoB with precision of decade (8) or century (7) will throw up unexpected year(?dob) values, as the second query shows.
SELECT ?age (sum(?countmale) as ?male) (sum(?countfemale) as ?female) WHERE {
  ?actor p:P569/psv:P569 ?birth_date_node .
  {?birth_date_node wikibase:timePrecision "9"^^xsd:integer .}
  UNION
  {?birth_date_node wikibase:timePrecision "10"^^xsd:integer .}
  UNION
  {?birth_date_node wikibase:timePrecision "11"^^xsd:integer .}
  ?birth_date_node wikibase:timeValue ?dob .
  ?actor wdt:P21 ?genre .
  ?actor wdt:P106 wd:Q33999 .
  # ?movie wdt:P495 wd:Q142 .
  ?actor wdt:P27 wd:Q142 .
  ?movie wdt:P161 ?actor .
  ?movie wdt:P577 ?dateMovie .
  BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) . 
  BIND(if(?genre=wd:Q6581072,1,0) as ?countfemale)
  BIND(if(?genre=wd:Q6581097,1,0) as ?countmale)
  FILTER(?age > 0) .
  FILTER(?age < 100) .
     }
  GROUP BY ?age
  ORDER BY ?age
Try it!
SELECT ?actor ?movie ?age ?dob (year(?dob) as ?yeardob) WHERE {
  ?actor p:P569/psv:P569 ?birth_date_node .
  {?birth_date_node wikibase:timePrecision "7"^^xsd:integer .}
  ?birth_date_node wikibase:timeValue ?dob .
  ?actor wdt:P106 wd:Q33999 .
  # ?movie wdt:P495 wd:Q142 .
  ?actor wdt:P27 wd:Q142 .
  ?movie wdt:P161 ?actor .
  ?movie wdt:P577 ?dateMovie .
  BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) . 
  FILTER(?age = 1) .
     }
  ORDER BY ?age
Try it! --Tagishsimon (talk) 08:16, 11 September 2018 (UTC)
Just a small simplification of the query by replacing the "UNION" with a "FILTER". Don't know if it makes the execution time shorter or maybe longer though.
I also included subclasses of actor (Q33999) that may be of interest.
SELECT ?age (sum(?countmale) as ?male) (sum(?countfemale) as ?female) WHERE {
  ?actor p:P569/psv:P569 [ wikibase:timePrecision ?tp; wikibase:timeValue ?dob ]. 
  FILTER(?tp>8) 
  ?actor wdt:P21 ?genre .
  ?actor wdt:P106/wdt:P279* wd:Q33999 .   #include subclasses of "actors" such as "child actors"
  ?actor wdt:P27 wd:Q142 .
  ?movie wdt:P161 ?actor .
  ?movie wdt:P577 ?dateMovie .
  BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) . 
  BIND(if(?genre=wd:Q6581072,1,0) as ?countfemale)
  BIND(if(?genre=wd:Q6581097,1,0) as ?countmale)
  FILTER(?age > 0) .
  FILTER(?age < 100) .
     }
  GROUP BY ?age
  ORDER BY ?age
Try it!
--Larske (talk) 08:37, 11 September 2018 (UTC)
@Tagishsimon, Larske: thanks for your help. Regarding the precision issue, that was the origin of some incoherences in the data I had, thanks! Regarding the solution given by Larske, that seems great but I believe it generates a new issue by counting twice entries like Dorothée Jemma (Q3037216) who have both actor (Q33999) and one of its subclass like dub actor (Q11481802). I have found what I think might be a fix to this issue :
SELECT ?age (sum(?countmale) as ?male) (sum(?countfemale) as ?female) WHERE {
  ?actor p:P569/psv:P569 [ wikibase:timePrecision ?tp; wikibase:timeValue ?dob ]. 
  FILTER(?tp>8) 
  ?actor wdt:P21 ?genre .
  {
    SELECT distinct ?actor WHERE {
        ?actor wdt:P106/wdt:P279* wd:Q33999 .   #include subclasses of "actors" such as "child actors"
      }
    }
  ?actor wdt:P27 wd:Q142 .
  ?movie wdt:P161 ?actor .
  ?movie wdt:P577 ?dateMovie .
  BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) . 
  BIND(if(?genre=wd:Q6581072,1,0) as ?countfemale)
  BIND(if(?genre=wd:Q6581097,1,0) as ?countmale)
  FILTER(?age > 0) .
  FILTER(?age < 100) .
     }
  GROUP BY ?age
  ORDER BY ?age
Try it!

If anybody thinks something can be improved on this query, that would be welcome! Thanks for your help. Symac (talk) 12:39, 11 September 2018 (UTC)

Call out to Mediawiki API GeoData from SPARQL[edit]

I am trying to call out to Mediawiki API GeoData from SPARQL to retrieve Wikimedia items in the 10km radius around some location in the middle of Manhattan. It doesn't bring any result. Any ideas?

SELECT ?item WHERE {
    SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam mwapi:generator "geosearch" .
      bd:serviceParam mwapi:ggscoord "40.751322|-73.978262" .
      bd:serviceParam mwapi:ggsradius "10000" .
      ?item wikibase:apiOutputItem mwapi:item .      
    }
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  } LIMIT 2

Try it!

Will be also grateful on advise how to format this request properly as all other requests on the page are formatted with proper highlights and "Try it" link. --Chainastole (talk) 10:54, 11 September 2018 (UTC)

@Chainastole: : I don't have the answer to the question but I have changed the formatting, you need to add {{SPARQL|query= before the query then }} after it. Symac (talk) 13:23, 11 September 2018 (UTC)
@Chainastole: here is a version that should work :
SELECT ?place ?placeLabel ?image ?coordinate_location ?dist ?instance_of ?instance_ofLabel WHERE {
  SERVICE wikibase:around {
    ?place wdt:P625 ?coordinate_location.
    bd:serviceParam wikibase:center "Point(-73.978262 40.751322)"^^geo:wktLiteral.
    bd:serviceParam wikibase:radius "1".
    bd:serviceParam wikibase:distance ?dist.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?place wdt:P18 ?image. }
  OPTIONAL { ?place wdt:P31 ?instance_of. }
}
Try it! (limited to 1km to improve performance). Symac (talk) 13:54, 11 September 2018 (UTC)

Thank you, Symac for formatting. I am aware of this option. The issue I want the initial search of the items in the vicinity of provided coordinate be performed in Wikimedia - not Wikidata. This way I hope to receive better contextual relevance of the search. Additionally I would like to receive some information (like pageviews) which is accessible on Wikimedia but isn't on Wikidata. Chainastole (talk) 15:09, 11 September 2018 (UTC)

@Chainastole: the cure might be as simple as adding an extra 'g' to a couple of your parameters - which I've done in your original query. See paragraph 3 of https://www.mediawiki.org/wiki/API:Query#Generators which says "Parameters passed to a generator must be prefixed with a g. For instance, when using generator=backlinks, use gbltitle instead of bltitle." --Tagishsimon (talk) 15:31, 11 September 2018 (UTC)
@Tagishsimon: This really fixed the problem. The Generator service now really returns the Wikimedia item to SPARQL query, where I conveniently retrieve more Wikidata related to it. My next problem is I need some info from Wikimedia, which is not accessible on Wikidata (like pageviews) which seem not to be supported MWAPI services. MWAPI Generator service only seems to be able to return the most basic item info like its URL, ns, pageid and title. I see at "Supported services" paragraph at https://www.mediawiki.org/wiki/Wikidata_Query_Service/User_Manual/MWAPI that Generator service is supposed to receive "prop" as an input but when providing it like below it didn't work.
SELECT ?item ?itemLabel ?pageviews WHERE {
    SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam mwapi:prop "pageviews" .
      bd:serviceParam mwapi:pvipdays "2" .
      bd:serviceParam mwapi:generator "geosearch" .
      bd:serviceParam mwapi:ggscoord "32.086169|34.876460" .
      bd:serviceParam mwapi:ggsradius "1000" .
      bd:serviceParam wikibase:limit 10 .
      ?item wikibase:apiOutputItem mwapi:item .
      ?pageviews wikibase:apiOutput "@pageviews" .
    }
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }

Try it!

--Chainastole (talk) 10:42, 15 September 2018 (UTC)

BarChart fail[edit]

I've failed to grok bar charts. Here's the Age of Actors query from above, which now returns two rows per year of age, one with a count of male and the other a count of female actors. I'm wanting a bar chart with age on the x-axis, bars for the count of male and female actors for each age, and a legend saying Male/Female. Instead I get a couple of dots and a raspberry. Pointers please.

#defaultView:BarChart
SELECT ?age (count(?age) as ?count) ?Label WHERE {
  ?actor p:P569/psv:P569 [ wikibase:timePrecision ?tp; wikibase:timeValue ?dob ]. 
  FILTER(?tp>8) 
  ?actor wdt:P21 ?genre .
  ?actor wdt:P106/wdt:P279* wd:Q33999 .   #include subclasses of "actors" such as "child actors"
  ?actor wdt:P27 wd:Q142 .
  ?movie wdt:P161 ?actor .
  ?movie wdt:P577 ?dateMovie .
  BIND(YEAR(?dateMovie)-YEAR(?dob) as ?age) .
  BIND(if(?genre=wd:Q6581072,"female","male") as ?Label)
  FILTER(?age > 0) .
  FILTER(?age < 100) .
     }
  GROUP BY ?age ?Label
  ORDER BY ?age ?Label

Try it! --Tagishsimon (talk) 22:53, 11 September 2018 (UTC)

@Tagishsimon: I think you’re seeing phabricator:T168341, unfortunately. It can be worked around by selecting (STR(?age) AS ?age_) instead of ?age, but that means that the X axis is no longer ordered or spaced according to its values. --TweetsFactsAndQueries (talk) 11:40, 12 September 2018 (UTC)
Age of male vs female actors at time of film release
Thanks TweetsFactsAndQueries. User:Smalyshev (WMF), is there any possibility of movement on this? We have the data to make useful barchart contributions on matters of contemporary interest, such as that to the right, but seem to lack the means. What's the story? --Tagishsimon (talk) 16:24, 12 September 2018 (UTC)

Fetch qualifiers[edit]

Hi all,

I would like to make a query that fetches end- and start time of the exhibitions where a work has been shown. For example from this item: https://www.wikidata.org/wiki/Q22250456. I can't seem to figure it out.

Kind regards. Olivier

@Oliviervd: Here's a basic query, looking for exhibitions of installations. hth.
SELECT ?item ?itemLabel ?locationLabel ?countryLabel ?startdate ?enddate WHERE {
  ?item wdt:P31 wd:Q20437094.    #is an installation
  ?item p:P608 ?statement.       # has P608 data
  optional {?statement pq:P276 ?location.} 
  optional {?statement pq:P17 ?country.}
  optional {?statement pq:P580 ?startdate.}
  optional {?statement pq:P582 ?enddate.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } order by ?itemLabel ?startdate
Try it! --Tagishsimon (talk) 15:32, 12 September 2018 (UTC)

Fetch items from Zika Corpus[edit]

I am trying to fetch items from the Zika Corpus. I formulated the query below with help from participants of the Wikimania Hackathon. Unfortunately, however, it still contains duplicates, most likely stemming from the GROUP_CONCAT statements. In addition, i could not get the main_subjectLabel to show up in the results. Can you help?

SELECT DISTINCT ?item ?_PubMed_ID ?PMCID ?publication_date ?published_in ?DOI ?itemLabel
?published_inLabel (GROUP_CONCAT(DISTINCT ?author_name_string; SEPARATOR = "; ") AS ?authors) (GROUP_CONCAT(DISTINCT ?main_subject; SEPARATOR = "; ") AS ?subject) WHERE {
{
  SELECT * WHERE {
    ?item wdt:P31 wd:Q13442814.
    ?item rdfs:label ?itemLabel.
    FILTER(CONTAINS(LCASE(?itemLabel), "zika"))
    FILTER((LANG(?itemLabel)) = "en")
  }
LIMIT 1000
}
OPTIONAL { ?item wdt:P698 ?_PubMed_ID. }
OPTIONAL {
?item wdt:P1433 ?published_in.
?published_in rdfs:label ?published_inLabel.
}
OPTIONAL { ?item wdt:P921 ?main_subject. }
OPTIONAL { ?item wdt:P932 ?PMCID. }
OPTIONAL { ?item wdt:P577 ?publication_date. }
OPTIONAL { ?item wdt:P921 ?published_in. }
OPTIONAL { ?item wdt:P2093 ?author_name_string. }
OPTIONAL { ?item wdt:P356 ?DOI }
}
GROUP BY ?item ?itemLabel ?_PubMed_ID ?PMCID ?publication_date ?published_in ?published_inLabel ?DOI
LIMIT 100

Try it!

@Pkraker: There were (and still are in the reworked version below) several areas where duplicates could arise: in essence, any selected variable which cannot be guaranteed to be unique can cause dupes; and of the variables used, only ?item and ?itemLabel were constrained to be unique. For all those that are not guaranteed unique, an aggregate function such as group_concat is needed to facilitate a one-row-per-item result. I have added one or two more group_concats for variables I noted to be causing dupes; and in addition constrained ?published_inLabel and ?main_subjectLabel to select EN values only ... I think one of those I invented to solve the main_subjectLabel issue, the other was pre-existing ... both would cause dupes if there were other language rdfs:label values for the item, ifthe EN filter was lacking. So now, if there are further dupes, they'll arise from mutiple values associated with the statements populating any of ?_PubMed_ID ?PMCID ?publication_date ?DOI, and the cure will be to group_concat the affected variable and remove it from the group by clause.
SELECT DISTINCT ?item ?_PubMed_ID ?PMCID ?publication_date ?DOI ?itemLabel
(GROUP_CONCAT(DISTINCT ?published_in; SEPARATOR = "; ") AS ?publicationQId)  
(GROUP_CONCAT(DISTINCT ?published_inLabel; SEPARATOR = "; ") AS ?publication)  
(GROUP_CONCAT(DISTINCT ?author_name_string; SEPARATOR = "; ") AS ?authors) 
(GROUP_CONCAT(DISTINCT ?main_subjectLabel; SEPARATOR = "; ") AS ?subject) WHERE {
{
  SELECT * WHERE {
    ?item wdt:P31 wd:Q13442814.
    ?item rdfs:label ?itemLabel.
    FILTER(CONTAINS(LCASE(?itemLabel), "zika"))
    FILTER((LANG(?itemLabel)) = "en")
  }
LIMIT 1000
}
OPTIONAL { ?item wdt:P698 ?_PubMed_ID. }
OPTIONAL { ?item wdt:P1433 ?published_in.
          ?published_in rdfs:label ?published_inLabel.
          FILTER((LANG(?published_inLabel)) = "en")
          }
OPTIONAL { ?item wdt:P921 ?main_subject. ?main_subject rdfs:label ?main_subjectLabel. FILTER((LANG(?main_subjectLabel)) = "en")}
OPTIONAL { ?item wdt:P932 ?PMCID. }
OPTIONAL { ?item wdt:P577 ?publication_date. }
OPTIONAL { ?item wdt:P921 ?published_in. }
OPTIONAL { ?item wdt:P2093 ?author_name_string. }
OPTIONAL { ?item wdt:P356 ?DOI }
}
GROUP BY ?item ?itemLabel ?_PubMed_ID ?PMCID ?publication_date ?DOI
LIMIT 100
Try it! --Tagishsimon (talk) 20:16, 12 September 2018 (UTC)
@Tagishsimon: Awesome, thanks! --Pkraker (talk) 12:54, 15 September 2018 (UTC)

Finding all municipalities of Austria.[edit]

I'm trying to find all municipalities of Austria with their coordinates and the population. This query:

PREFIX psv: <http://www.wikidata.org/prop/statement/value/>

SELECT DISTINCT ?itemLabel (AVG(?item_lat) AS ?lat) (AVG(?item_long) AS ?long) (AVG(?Einwohnerzahl) AS ?pop) WHERE {
  ?item wdt:P31 wd:Q667509.
  ?item rdfs:label ?itemLabel.
  ?item (p:P625/psv:P625) ?item_node.
  ?item_node wikibase:geoLatitude ?item_lat.
  ?item_node wikibase:geoLongitude ?item_long.
  ?item wdt:P1082 ?Einwohnerzahl.
  FILTER((LANG(?itemLabel)) = "de")
}
GROUP BY ?itemLabel
ORDER BY ?itemLabel

Try it!

works fine, but includes municipalities that no longer exist such as: https://www.wikidata.org/wiki/Q691290 How can I exclude these municipalities where instance of municipality of Austria has an end time?

@Mkhum: You need to use different property paths to get to qualifiers, like this:
SELECT DISTINCT ?itemLabel (AVG(?item_lat) AS ?lat) (AVG(?item_long) AS ?long) (AVG(?Einwohnerzahl) AS ?pop) WHERE {
#  ?item wdt:P31 wd:Q667509.
  ?item p:P31 ?statement.
  ?statement ps:P31 wd:Q667509.
  filter not exists {?statement pq:P582 [].}
  ?item rdfs:label ?itemLabel.
  ?item (p:P625/psv:P625) ?item_node.
  ?item_node wikibase:geoLatitude ?item_lat.
  ?item_node wikibase:geoLongitude ?item_long.
  ?item wdt:P1082 ?Einwohnerzahl.
  FILTER((LANG(?itemLabel)) = "de")
}
GROUP BY ?itemLabel
ORDER BY ?itemLabel
Try it! --Tagishsimon (talk) 16:39, 13 September 2018 (UTC)

Perfect. Thank you!

UTübingen people[edit]

Could someone please check the code of this list:

@Daniel Mietchen: The query is based on Wikidata:University of Virginia/Listeria/UVa people. --Einsteiger (talk) 11:32, 13 September 2018 (UTC)

@Einsteiger: It was missing {{Wikidata list end}}. Now fixed. --Tagishsimon (talk) 16:43, 13 September 2018 (UTC)
@Tagishsimon: Thanks! I would like to have one small improvement. How can I display date of birth (P569) = year of birth (without day and month)? Is this possible? --Einsteiger (talk) 23:08, 13 September 2018 (UTC)
@Einsteiger: I've added this as a new column. Remove the P569, if you want to remove the full DoB column. --Tagishsimon (talk) 23:26, 13 September 2018 (UTC)
@Tagishsimon: Can you take another look at the list? I've tried to add P570 (?dodyear:death) but something went wrong. --Einsteiger (talk) 02:02, 14 September 2018 (UTC)
@Einsteiger: Your change looked fine to me, but as it didn't work, I reverted to my last change, and then implemented year of death exactly as you did, and it worked. Now you get to re-add the See Also, remove any columns you don't want, and change column headers. Good luck. --Tagishsimon (talk) 02:21, 14 September 2018 (UTC)
Perfect. Thank you so much! --Einsteiger (talk) 03:03, 14 September 2018 (UTC)

Please help with qualifier labels[edit]

Hi. I'm sure the solution to this problem is very simple but i cannot figure it out. I have written a short query here which gives me the results i want (start and end dates for ownership of all merchant ships owned by Thomas Jones, however, i CANNOT get it to give me the label for the names of the ships. I'm probably going about it the completely wrong way. If someone could help i would be very grateful. Thanks Jason.nlw (talk) 22:27, 13 September 2018 (UTC)

@Jason.nlw: To be pedantic, your query returns all things owned by the owner of Edward John (Q24027085); which in this instance is the same as all merchant ships owned by Thomas Jones, but only 'cos that is all he is listed as owning, and because the Edward John had only one owner. (I'm sure you know all this :). With that preamble, here's the reworked query, in which I've gratuitously made the start & end optional. In short, a pq: or a ps: is linked to a p: via a statement, and so you need to get the statement (which you got as ?do) and then use that to get the pq: - which you successfully did, but also to get the ps: which you did not do ... perhaps misleading yourself that your ?do should in some way have been the QId of the thing owned. Something like that.
SELECT ?item ?itemLabel ?start ?end ?doLabel WHERE 
{
  ?item wdt:P1830 wd:Q24027085 . 
  ?item p:P1830 ?statement .
  ?statement ps:P1830 ?do.
  optional {?statement pq:P580 ?start .}
  optional {?statement pq:P582 ?end .}
  SERVICE wikibase:label {bd:serviceParam wikibase:language "en" .}
}
Try it! --Tagishsimon (talk) 22:45, 13 September 2018 (UTC)
@Tagishsimon: This is great! many thanks for your help. Jason.nlw (talk) 07:53, 14 September 2018 (UTC)

Taxon authors[edit]

Hello, I was wondering how I can search for species that have been described by a particular person (taxon author). I have tried doing Wikidata queries for taxon author for John Gould and nothing comes up. Does this mean the data is missing or that I'm not using the right query? Thank you! Nicolekearney (talk) 06:30, 14 September 2018 (UTC)

@Nicolekearney: The second of those two options ;). It's done like this; the first, abbreviated, query reads, 'the item has a P225 property; that property has a P225 value of the taxon name, and has a P405 qualifier which is John Gould's QId.'
You can follow the logic of the query through the data model ... p: gets you from the item to a statement, and pq: or ps: get you from the statement to the simple values (taxon name, John's QId) associated with the statement. By contrast, it is not possible to go down the wdt: path and get to the pq: for the statement. The square brackets in the query are a simplification ... the query can also be written as per the second example in which we explicitly fetch the ?statement and display its ID in a column, and use it to get the ps: and the pq: values. The statement ID is just an ugly URI which serves as a link between the item QId and the values associated with item statements.
SELECT ?item ?taxon WHERE 
{
  ?item p:P225 [ps:P225 ?taxon; pq:P405 wd:Q313787].
}
Try it!
SELECT ?item ?statement ?taxon WHERE 
{
  ?item p:P225 ?statement.
  ?statement ps:P225 ?taxon.
  ?statement pq:P405 wd:Q313787.
}
Try it! --Tagishsimon (talk) 07:50, 14 September 2018 (UTC)

Anti-vandalism[edit]

I would like to follow label and description changes in Hungarian, making my life a bit easier. I am not sure about that a query is what I am searching, so feel free to recommend me other options. Only edits of non-autoconfirmed users and anons should be included. Showing many edits at once (e.g. ~50) would be important, because my eyes could work faster and spotting obvious mistakes/problems would be easier.

I imagine it something like this: item • P31/P279 (for those cases, where I don't know what the item is about) • label and description (+showing the changes, if it is possible) • user

Would it be possible? Bencemac (talk) 08:02, 15 September 2018 (UTC)

There are a couple of tools for this purpose listed at Wikidata:WikiProject Counter-Vandalism. I recommend to have a look at reCh and its filters, but there are others available as well (DiffLists, ORC, etc.). —MisterSynergy (talk) 08:16, 15 September 2018 (UTC)
@MisterSynergy: If I see right, wdwd is the only one which can list changes in Hungarian-only. Am I right? It is a little bit slow and reverted changes are still appearing, but it is more than nothing. Thanks, Bencemac (talk) 11:35, 15 September 2018 (UTC)
Please open reCh again, log in (upper right corner) and reload the page. Select “Type of edits: terms” and enter “hu” in the “enter language codes” form field to see Hungarian edits only. You can conveniently undo und revert from the reCh interface, or patrol if edits are okay. —MisterSynergy (talk) 11:38, 15 September 2018 (UTC)
You are right, I was blind. Thank you very much, this is what I searched. Bencemac (talk) 11:48, 15 September 2018 (UTC)
I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Bencemac (talk) 11:48, 15 September 2018 (UTC)

Only one item to one property[edit]

Hi! Is there any possibility to specify, that I want only one item to one property? For example when I want to add description Belgian tennis player, it will mostly return Belgian players, when I will specify country of citizenship: Belgium. But there will be also e.g. French-Belgian players, which have Belgium in this property, but they also have another country there. So if I can specify just e.g. Belgium. Thank for help! --Luky001 (talk) 17:11, 15 September 2018 (UTC)

@Luky001: like this; we do a two stage query, winnowing the results of the first query in the second:
SELECT ?item ?itemLabel ?itemDescription
with { SELECT ?item (count(distinct ?citizenships) as ?citizenship_count) WHERE 
{
  ?item wdt:P106 wd:Q10833314 . # they're a tennis player
  ?item wdt:P27 wd:Q31.         # they're Belgian
  ?item wdt:P27 ?citizenships.  # get any & all of their citizenships - this creates one row per citizenship
} group by ?item  }             # we group by ?item and (see above) count the number of ?citizenships for the item
as %i                           # we take the results of this named query - a list of ?item and ?citizenship_count
where 
{
  include %i                    # and run a second query over the results
  filter (?citizenship_count<2) # rejecting rows where the count is more than 1
  optional {?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="fr")}                     # get labels
  optional {?item schema:description ?itemDescription. filter(lang(?itemDescription)="fr")} # get descriptions
}
Try it! --Tagishsimon (talk) 19:50, 15 September 2018 (UTC)
@Tagishsimon: you can simplify this using HAVING:
SELECT ?item ?itemLabel ?itemDescription WHERE {
  ?item wdt:P106 wd:Q10833314;
        wdt:P27 wd:Q31;
        wdt:P27 ?citizenship.
  OPTIONAL { ?item rdfs:label ?itemLabel. FILTER(LANG(?itemLabel)="fr") }
  OPTIONAL { ?item schema:description ?itemDescription. FILTER(LANG(?itemDescription)="fr") }
}
GROUP BY ?item ?itemLabel ?itemDescription
HAVING(COUNT(DISTINCT ?citizenship) = 1)
Try it! --TweetsFactsAndQueries (talk) 11:51, 16 September 2018 (UTC)
Thank you, TFAQ. That's a useful thing to learn; I was hitherto mainly unaware of HAVING. --Tagishsimon (talk) 12:37, 16 September 2018 (UTC)

Combining entity and label into linked label[edit]

I often use both an entity and its label as two columns in a tabular result, e.g. in this list of Danish monarchs:

SELECT ?person ?personLabel WHERE {
  ?person wdt:P39 wd:Q18341329
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

With a large number of items in the select clause two columns for the same concept take up a lot of space, and the entity itself may be a bit cryptic for people not familiar with Wikidata. Is there a way to construct a single column whose entries have the label as their text and are also HTML links to the corresponding Wikidata entity pages? In the example, that would be one column, the first row having Abel as text with a link to https://www.wikidata.org/wiki/Q27932?--109.91.86.179 14:13, 16 September 2018 (UTC)

That’s not yet possible for the Table result view, though various other result views (e. g. Map, Image grid, Timeline) already combine two columns into one as long as they follow the default naming scheme (?whateverLabel). --TweetsFactsAndQueries (talk) 15:50, 16 September 2018 (UTC)

Text filter OR like[edit]

Hello, I'd like to get multiples rows given specified text. Is there a way to write DLO or ALA (but writing UNION) ?

SELECT distinct ?item ?itemLabel  WHERE {

{?item wdt:P238  "DLO", "ALA" .} #item has a P238 value                 

SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values

}

Try it! Bouzinac (talk) 11:08, 18 September 2018 (UTC)

SELECT ?item ?itemLabel ?iataCode WHERE {
  VALUES ?iataCode { 'DLO' 'ALA' }
  ?item wdt:P238 ?iataCode .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
Try it!
Mind that there is no item with IATA airport code (P238): "DLO". —MisterSynergy (talk) 11:24, 18 September 2018 (UTC)
Perfect! Thanks. Bouzinac (talk) 11:48, 18 September 2018 (UTC)

media legend[edit]

Hello.

1) I want to find the items that:

a) have Statistical Service of Cyprus Geocode (P4812) withanyvalue AND

b) have locator map image (P242) with anyvalue

but have not any media legend (P2096) as a qualifier for P242.

SELECT ?item ?itemLabel  WHERE {
  ?item wdt:P4812 [].
  filter not exists {?item p:P242 [pq:P2096 [] ]. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
Try it!

2) I want to find the items that:

a) have P4812 withanyvalue AND

b) have P242 with anyvalue

but have P2096 as a qualifier for P242 ONLY for greek language (el).

SELECT ?item ?itemLabel  WHERE {
  ?item wdt:P4812 [].
  ?item p:P242 [pq:P2096 ?val ]. filter(lang(?val)="el")
  filter not exists {?item p:P242 [pq:P2096 ?val2 ]. filter(lang(?val2)!="el")}
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
Try it!


3) I want to find the items that:

a) have P4812 withanyvalue AND

b) have P242 with anyvalue

but have P2096 as a qualifier for P242 ONLY for english language (en).

SELECT ?item ?itemLabel  WHERE {
  ?item wdt:P4812 [].
  ?item p:P242 [pq:P2096 ?val ]. filter(lang(?val)="en")
  filter not exists {?item p:P242 [pq:P2096 ?val2 ]. filter(lang(?val2)!="en")}
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
Try it!

Maybe only the second query have results. Xaris333 (talk) 13:37, 22 September 2018 (UTC)

@Xaris333: - as above. Let me know if you find issues. --Tagishsimon (talk) 14:04, 22 September 2018 (UTC)

@Tagishsimon: the first one is ok. The other are not. I want to find the item that have P242 in only one language (el or en). Now it shows me all the items have P242 in el or en, but they may have also P242 in other language. I want to have P242 ONLY for greek language (el) for the second query and to have P242 ONLY for english language (en) for the third query. Xaris333 (talk) 14:11, 22 September 2018 (UTC)

@Xaris333: Fixed, I hope. Let me know. --Tagishsimon (talk) 14:18, 22 September 2018 (UTC)

@Tagishsimon: it working! Thankxs Xaris333 (talk) 15:28, 22 September 2018 (UTC)

Commons[edit]

Hello.

I want to find the items that:

a) have Statistical Service of Cyprus Geocode (P4812) withanyvalue AND

b) have Commons category (P373) with anyvalue

but have not commonswiki link. Xaris333 (talk) 17:10, 22 September 2018 (UTC)

@Xaris333:
SELECT ?item ?itemLabel  WHERE {
  ?item wdt:P4812 [].
  ?item wdt:P373 [].
  filter not exists {?article schema:about ?item ; schema:isPartOf <https://commons.wikimedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
Try it! --Tagishsimon (talk) 17:49, 22 September 2018 (UTC)

Thanks! Xaris333 (talk) 17:54, 22 September 2018 (UTC)

Why does wikibase:sitelinks report 0 when there is a sitelink?[edit]

Can anyone explain why the links of the objects in the following query are not counted by wikibase:sitelinks.
wikibase:sitelinks reports 0, but I would expect 1 for these objects.

# all objects below have links to svwiki, but wikibase:sitelinks reports 0, what is wrong?
SELECT ?item ?sitelinks WHERE {
  VALUES ?item { wd:Q30160432 wd:Q30158495 wd:Q30163145 wd:Q30172324 wd:Q30170596 wd:Q30179545 wd:Q30173346 wd:Q30172516 wd:Q30160689 wd:Q30160689 wd:Q30161783 wd:Q30198361 wd:Q30170201 wd:Q30158592 }
  ?item wikibase:sitelinks ?sitelinks .
}

Try it!

--Larske (talk) 07:51, 23 September 2018 (UTC)

wikibase:sitelinks and wikibase:statements (and maybe also wikibase:identifiers) are known to be incorrect sometimes, see phab:T145712. —MisterSynergy (talk) 08:06, 23 September 2018 (UTC)
@MisterSynergy: Thanks for your quick reply. I notice that when I did an edit to one of the objects in the above query, the wikibase:sitelinks result was updated. Is there any better way to get the correct result? --Larske (talk) 08:32, 23 September 2018 (UTC)
I'm not sure if this necessarily reliable. You could add filter not exists {[] schema:about ?item } to the query. --- Jura 08:40, 23 September 2018 (UTC)
Unfortunately this doesn't seem to change the result, the objects with sitelinks, where wikibase:sitelinks reports 0, will still pass this FILTER. --Larske (talk) 09:22, 23 September 2018 (UTC)