Wikidata:Request a query/Archive/2017/04

This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Items without a label in French but with a linked category which has a label

Hi all,

I would like a SPARQL query which gives items without a label in French but with a linked category which has a label in French.

It would be useful to add labels in French.

Thank you, Tubezlob (🙋) 14:23, 1 April 2017 (UTC)

# Items that don't have a label in French, but there is a main category that has a label in French.
SELECT ?item ?itemLabel ?_topic_s_main_category ?_topic_s_main_categoryLabelFR WHERE {
  ?item wdt:P910 ?_topic_s_main_category.
  ?_topic_s_main_category rdfs:label ?_topic_s_main_categoryLabelFR filter (lang(?_topic_s_main_categoryLabelFR) = "fr") .
  FILTER NOT EXISTS {?item rdfs:label ?itemLabelFR filter (lang(?itemLabelFR) = "fr")} .
  }
LIMIT 1000

Try it!

Shinnin (talk) 15:43, 1 April 2017 (UTC)

@Shinnin: Many thanks! Tubezlob (🙋) 16:29, 1 April 2017 (UTC)

items without P31

How do I run a query with all items without instance of (P31)? --Bigbossfarin (talk) 20:09, 1 April 2017 (UTC)

SELECT ?item WHERE {
  ?item wikibase:sitelinks [] .
  MINUS { ?item (wdt:P31|wdt:P279) [] } .
} LIMIT 1000

Try it!

Explained here. Matěj Suchánek (talk) 20:14, 1 April 2017 (UTC)

Thank you! --Bigbossfarin (talk) 20:15, 1 April 2017 (UTC)

Members of the Tweede Kamer at some point in time

I'm working on member of the House of Representatives of the Netherlands (Q18887908) and I'm trying to make a query to show the members at a point in time. I got the two parts working

People where the point in time is between the time they entered and left.

SELECT ?item ?itemLabel WHERE {
  BIND("2010-05-00T00:00:00Z"^^xsd:dateTime AS ?pointintime) .
  ?item p:P39 ?positionstatement .
  ?positionstatement ps:P39 wd:Q18887908 .
  ?positionstatement pq:P580 ?startdate .
  ?positionstatement pq:P582 ?enddate . 
  FILTER(?startdate < ?pointintime && ?pointintime < ?enddate )  .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } 
  } ORDER BY ?itemLabel

Try it!

People where the point in time is after they entered and these people are still a member now.

SELECT ?item ?itemLabel WHERE {
  BIND("2010-05-00T00:00:00Z"^^xsd:dateTime AS ?pointintime) .
  ?item p:P39 ?positionstatement .
  ?positionstatement ps:P39 wd:Q18887908 .
  ?positionstatement pq:P580 ?startdate .
  MINUS { ?positionstatement pq:P582 ?enddate } . 
  FILTER(?startdate < ?pointintime ).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } 
  } ORDER BY ?itemLabel

Try it!

Now I want to combine these two queries (UNION), but that seems to fail. Thank you, Multichill (talk) 21:21, 1 April 2017 (UTC)

SELECT DISTINCT ?item ?itemLabel WHERE {
  {
    SELECT ?item {
      BIND("2010-05-00T00:00:00Z"^^xsd:dateTime AS ?pointintime) .
      ?item p:P39 ?positionstatement .
      ?positionstatement ps:P39 wd:Q18887908 .
      ?positionstatement pq:P580 ?startdate .
      MINUS { ?positionstatement pq:P582 ?enddate } . 
      FILTER( ?startdate < ?pointintime ) .
    }
  } UNION {
    SELECT ?item WHERE {
      BIND("2010-05-00T00:00:00Z"^^xsd:dateTime AS ?pointintime) .
      ?item p:P39 ?positionstatement .
      ?positionstatement ps:P39 wd:Q18887908 .
      ?positionstatement pq:P580 ?startdate .
      ?positionstatement pq:P582 ?enddate . 
      FILTER( ?startdate < ?pointintime && ?pointintime < ?enddate ) .
    }
  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} ORDER BY ?itemLabel

Try it!

Matěj Suchánek (talk) 08:14, 2 April 2017 (UTC)

@Matěj Suchánek: thanks! I'm pretty sure I tried that and it returned zero results, I must have made a mistake in it somewhere. Do you know a way to only have one bind for ?pointintime ? I did manage to make this one to show progress:

See Wikidata query.

(should be 150) Multichill (talk) 10:45, 2 April 2017 (UTC)

I hope this returns the same results:

SELECT ?pointintime (COUNT(?item) AS ?count) WHERE {
  ?month wdt:P31 wd:Q5151 .
  ?month wdt:P585 ?pointintime  .
  ?item p:P39 ?positionstatement .
  ?positionstatement ps:P39 wd:Q18887908 .
  ?positionstatement pq:P580 ?startdate .
  FILTER( ?startdate < ?pointintime ) .
  OPTIONAL { ?positionstatement pq:P582 ?enddate } .
  FILTER( !BOUND( ?enddate )|| ?pointintime < ?enddate ) .
} GROUP BY ?pointintime ORDER BY ?pointintime

Try it!

Matěj Suchánek (talk) 10:55, 2 April 2017 (UTC)

See Wikidata query.

Had to wrap it in a DISTINCT and do some evil wiki trick ({{!}}) to get it to work. Graphs seem to look the same. Multichill (talk) 11:06, 2 April 2017 (UTC)

Description different than

I need a filter to detect item with description in language different than a specific string. I tried with this query but don't work, any suggestion?

SELECT ?item ?itDescrizione
WHERE
{
    ?item wdt:P31 wd:Q4167836 .
    ?item schema:description ?itDescrizione. FILTER(lang(?itDescrizione)="it").
    FILTER(?itDescrizione!="categoria di un progetto Wikimedia")
    #FILTER(false=CONTAINS(?itDescrizione,"categoria di un progetto Wikimedia"))
    #FILTER(?itDescrizione!=STR("categoria di un progetto Wikimedia"))
    #FILTER(?itDescrizione!="categoria di un progetto Wikimedia"^^xsd:string)
} LIMIT 100

Try it!

--ValterVB (talk) 15:36, 2 April 2017 (UTC)

You can do more queries on ranges of Qids like this:

SELECT ?item ?itDescrizione WITH {
  SELECT ?item {
    BIND( 1000000 * 13 AS ?base ) . # change this 0-30
    ?item wdt:P31 wd:Q4167836 .
    BIND( xsd:integer( STRAFTER( STR( ?item ), STR( wd:Q ) ) ) AS ?num ) .
    FILTER( ?num > ?base + 0 && ?num < ?base + 1000001 ) .
  }
} AS %sub WHERE {
  INCLUDE %sub .
  ?item schema:description ?itDescrizione FILTER( LANG( ?itDescrizione ) = 'it' ) .
  FILTER( STR( ?itDescrizione ) != 'categoria di un progetto Wikimedia' ) .
}

Try it!

Matěj Suchánek (talk) 16:43, 2 April 2017 (UTC)

SELECT ?item ?itemLabel ?itemDescription WHERE {
  ?item wdt:P31 wd:Q4167836. 
  Filter (?itemDescription = "categoria di un progetto Wikimedia") 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". } 
}
LIMIT 10

Try it!

Maybe this will work? It gives no matching results, but a bot is adding such descriptions in all latin languages, so that is why there may be no results... Q.Zanden questions? 17:12, 2 April 2017 (UTC)

It work, I found some item with error (example), but in some case I have time out. It's possible move ?item wdt:P31 wd:Q4167836 . in the second part of the querty? Maybe can be more efficient. --ValterVB (talk) 17:39, 2 April 2017 (UTC)

But that is only a temporary example. That statement is already deleted. But maybe, if you create a test item with instance of (P31) ->Wikimedia category (Q4167836), but without a description... Just to test if the query works. Q.Zanden questions? 17:53, 2 April 2017 (UTC)

The query work, I have already fixed some items. --ValterVB (talk) 18:02, 2 April 2017 (UTC)

Get all newspapers in USA

I would like to collect all newspapers in USA . I have written a query to get newspapers. but its not listing out all available newspapers from wiki.

QUERY - version 1:

SELECT ?newspaper ?newspaperLabel  ?link WHERE {
  ?newspaper wdt:P31 wd:Q11032 .
  ?newspaper wdt:P856 ?link.
  ?newspaper wdt:P17 wd:Q30.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
 FILTER(NOT EXISTS { ?newspaper wdt:P576 ?date. })}

Try it!

Not all the newspaper is falls under major category as newspaper(Instance of newspaper) . some of them are in different categories like daily newspaper , weekly newspaper . Thus i have added those category(actually i should say this as sub-category of newspaper) in the code.

QUERY - version 2:

SELECT ?newspaper ?newspaperLabel ?link WHERE {
  {?newspaper wdt:P31 wd:Q1331793} UNION
  {?newspaper wdt:P31 wd:Q2138556} union  
  {?newspaper wdt:P31 wd:Q2305295}union  
  {?newspaper wdt:P31 wd:Q1110794}union  
  {?newspaper wdt:P31 wd:Q15265344}union  
  {?newspaper wdt:P31 wd:Q11032}union  
  {?newspaper wdt:P31 wd:Q1002697}.  
  ?newspaper wdt:P17 wd:Q30.
  ?newspaper wdt:P856 ?link. 
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,ar,be,bg,bn,ca,cs,da,de,el,en,es,et,fa,fi,he,hi,hu,hy,id,it,ja,jv,ko,nb,nl,eo,pa,pl,pt,ro,ru,sh,sk,sr,sv,sw,te,th,tr,uk,yue,vec,vi,zh" . }
 FILTER(NOT EXISTS { ?newspaper wdt:P576 ?date. })}

Try it!

Still its not listing out all the newspapers.

Though some of them are instance of 'newspaper',Query is not listing out them in result. Example : The Alabama Observer : https://www.wikidata.org/wiki/Q7712961 Query : version 2 is not listed above newspaper even though it is instance of newspaper.

Please help me to resolve this problem  – The preceding unsigned comment was added by WikiCompBot (talk • contribs).

Q7712961 does not have filled country (P17). You may use PetScan (and enwiki category "Newspapers published in the United States by state" and its subcategories to fill these gaps in Wikidata. --Jklamo (talk) 08:40, 29 March 2017 (UTC)

   Thanks a lot dude. Its working fine. One more help. When i try the same query for different countries , its not giving any results.
   For Ex : Newspapers published in the China by state
   Any Help Would Be Appreciated!!

Why do you use country (P17) in newspapers at all?? I think that country of origin (P495) is more suitable for creative works. --Infovarius (talk) 08:39, 4 April 2017 (UTC)

Count scripts ?

• Items used: George Washington (Q23)   , Q22283033

• Properties used: writing system (P282)   , Wikimedia language code (P424)   , P794

SELECT
	?writingsystem
	?writingsystemLabel
	(COUNT( DISTINCT ?l) as ?ct)
WHERE
{
	wd:Q23 rdfs:label ?label
	BIND( lang(?label) as ?lang)
	?l p:P424 [ ps:P424 ?lang ; pq:P794 wd:Q22283033 ] .
  	?l wdt:P282 ?writingsystem .
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY
	?writingsystem
	?writingsystemLabel
ORDER BY DESC(?ct)

Try it!

How could we improve the above? Ideally the output would include just Latin script instead of Spanish alphabet (Q285126).
--- Jura 04:06, 4 April 2017 (UTC)

#Number of films by year and genre

There is already an example querry:

#Number of films by year and genre
#defaultView:ScatterChart
SELECT ?year (COUNT(?_genre) AS ?count) (SAMPLE(?_genreLabel) AS ?label) (?year AS ?year_shown) WHERE {
  ?item wdt:P31 wd:Q11424.
  ?item wdt:P577 ?_publication_date.
  ?item wdt:P136 ?_genre.
  ?_genre rdfs:label ?_genreLabel.
  BIND(STR(YEAR(?_publication_date)) AS ?year)
  FILTER((LANG(?_genreLabel)) = "en")
  FILTER(?_publication_date >= "2000-00-00T00:00:00Z"^^xsd:dateTime)
}
GROUP BY ?_genreLabel ?year
HAVING (?count > 30)

Try it!

I would like to have this Graph starting from 1900 and displayed as stacked lines/areas. --Zulu55 (talk) 09:50, 4 April 2017 (UTC)

Item without a page in a specific wiki

I want a list with all american philosophers, but without a link to the dutch wikibooks (nl.wikibooks.org). How to do this? My begin query:

#philosophers with american nationality, without link to dutch wikibooks
SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P106 wd:Q4964182.  #Occupation : philosopher
  ?item wdt:P27 wd:Q30.     #nationality: USA
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
  #and the next step is to filter the items with an existing link to nl.wikibooks.org
}
LIMIT 200

Try it!

--Q.Zanden questions? 20:25, 4 April 2017 (UTC)

I found it, but I don't understand why it is not correct sorting...

SELECT DISTINCT ?item ?itemLabel ?article WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P106 wd:Q4964182.
  ?item wdt:P27 wd:Q30.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
  FILTER(NOT EXISTS {
    ?article schema:about ?item.
    ?article schema:isPartOf <https://nl.wikibooks.org/>.
  })
}
ORDER BY (?itemLabel)

Try it!

---Q.Zanden questions? 20:42, 4 April 2017 (UTC)

Notable integers, and for each the list of their classes

I want to create a list of famous integer, showing what classes they belong to, for instance "4 | square number, tetrahedral number, natural number" (or similar).

I thought this would do the trick, but unfortunately it only returns a single row, do you know why?

# Notable integers
SELECT
    (SAMPLE(?item) as ?item)
    (GROUP_CONCAT(?class; SEPARATOR = " , ") AS ?classes)
WHERE {
    ?item p:P31/ps:P31/wdt:P279* wd:Q12503.
    ?item p:P31 ?class.
}

Try it!

I don't mind getting many classes like "mathematical concept" or "entity", but I am also OK with only displaying the classes that are instances of set (Q36161). Thanks a lot! Syced (talk) 11:27, 5 April 2017 (UTC)

I fixed the problem like this:

# Notable integers
SELECT * WHERE {
  SELECT
    (SAMPLE(?item) as ?item)
    (SAMPLE(?itemLabel) as ?itemLabel)
    (SAMPLE(?value) as ?value)
    (GROUP_CONCAT(?class; SEPARATOR = " , ") AS ?classes)
  WHERE {
    ?item p:P31/ps:P31/wdt:P279* wd:Q12503.
    ?item p:P31 ?class.
    ?item wdt:P1181 ?value.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
  }
  GROUP BY ?item
}

Try it!

Thanks! Syced (talk) 11:41, 5 April 2017 (UTC)

Help on simple country query

Hi, I just wanted a list of all countries and did this simple search:

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>

SELECT DISTINCT ?item ?itemLabel
WHERE {
    ?item wdt:P31 wd:Q6256 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Why isn't Saudi Arabia (Q851) returned? 123 (talk) 17:47, 7 April 2017 (UTC)

If you take a closer look at the item, you will see it has instance of (P31): sovereign state (Q3624078) with preferred rank. If there's such a rank, it wipes out other statements with lower rank. If you wanted to match all the statements, you need to use ?item p:P31/ps:P31 wd:Q6256. And if anyone wanted to use this syntax and retain the wdt: behavior, they can use p:P31 [ a wikibase:BestRank; ps:P31 wd:Q6256 ]. Matěj Suchánek (talk) 18:51, 7 April 2017 (UTC)

start time of a position

Hi all,

This is my first request and I hope I am at the right place ...

I am looking for a query that would retrieve the start time of a held position.

In the case of Charles de GAULLE (Q2042), there are 4 positions available retrieved with the folowing request :

PREFIX bd: <http://www.bigdata.com/rdf#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
SELECT DISTINCT ?fonction ?fonctionLabel ?debut
WHERE
{wd:Q2042 wdt:P39 ?fonction.
 SERVICE wikibase:label
	{bd:serviceParam wikibase:language "fr"}
}

The start time is given by the property P580 for each position, but I don't see how to get it in a list like that :

fonction	fonctionLabel	debut
wd:Q191954	président de la République française	8 January 1959
wd:Q1587677	Premier ministre français	1 June 1958
wd:Q3409212	Président du Conseil	1 June 1958
wd:Q23004974	président du gouvernement provisoire de la République française	3 June 1944

Any help would be welcome !

Thanks a lot

Kiroule – The preceding unsigned comment was added by Kiroule (talk • contribs) at 9. 4. 2017, 17:21‎ (UTC).

SELECT ?fonction ?fonctionLabel ?debut {
  wd:Q2042 p:P39 [ ps:P39 ?fonction; pq:P580 ?debut ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}

Try it!

Matěj Suchánek (talk) 17:49, 9 April 2017 (UTC)

(ec) You need to use "p:P29" instead of "wdt:P29". p:P29 gets you a triple that has pq:P580 and ps:P29 . Some of the sample queries use these.
--- Jura 17:50, 9 April 2017 (UTC)

Super ! many thanks Matěj

Born in Europe

I wanted a list (or even better, a timeline) from all (male) composers born after 1500 in Europe. I made a start with this query:

#composers born after 1500
SELECT ?item ?itemLabel ?birth WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P21 wd:Q6581097.
  ?item wdt:P106 wd:Q36834.
  ?item wdt:P569 ?birth.
  bind ( ?birth >"1500" as ?birth).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
}

Try it!

How to create a timeline of it, and only born in europe? Thanks in advance! Q.Zanden questions? 21:38, 3 April 2017 (UTC)

• To get people born since 1500, try

  FILTER( YEAR( ?birth ) > 1499 ) .

or

    FILTER (?birth > "1499-01-01T00:00:00Z"^^xsd:dateTime) .

--- Jura 04:06, 4 April 2017 (UTC)

#composers born after 1500 in Europe
#defaultView:Timeline
SELECT DISTINCT ?item ?itemLabel ?birth  WHERE {
  ?item wdt:P31 wd:Q5;
  		wdt:P21 wd:Q6581097;
    	wdt:P106 wd:Q36834;
  		wdt:P569 ?birth;
  		wdt:P19 ?pob .
  ?pob wdt:P17 ?country.
  ?country wdt:P30 wd:Q46.
  FILTER( YEAR( ?birth ) > 1500 ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
}

Try it!

@QZanden:The part of the query that turns the list to a timeline is "#defaultView:Timeline". My client can't handle it with this query, probably because there are ~22k results. So, if the query above doesn't seem to work for you, remove the "#defaultView:Timeline" to simply get a list. Shinnin (talk) 03:17, 10 April 2017 (UTC)

@Shinnin: Thanks a lot! Q.Zanden questions? 16:09, 10 April 2017 (UTC)

How to search for all items used as publisher (P123) in any references?

Hope the subject line says it all. Unfortunatelly I don't have a clue about how even to begin ... 123 (talk) 22:21, 9 April 2017 (UTC)

SELECT DISTINCT ?publisher ?publisherLabel # ?entry ?entryLabel 
WHERE
{
  ?entry ?p ?statement .
  ?statement prov:wasDerivedFrom ?source.
  ?source pr:P123 ?publisher.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

If you want to include items where the references are made, just remove the hashtag from the first line of the query. Shinnin (talk) 02:23, 10 April 2017 (UTC)

Thanks a lot! 123 (talk) 13:57, 10 April 2017 (UTC)

Filter for integer values only

I want to make a query analog to "#Whose birthday is today?", but want to filter only "round" birthdays. For this purpose I divide the years until today by 10 and display them in a new column. The Filter should display only the lines with integer values. See here:

#Whose birthday is today?
SELECT ?entityLabel (YEAR(?date) AS ?year) (((YEAR(NOW())) - (YEAR(?date))) / 10 AS ?anniversary) WHERE {
  BIND(MONTH(NOW()) AS ?nowMonth)
  BIND(DAY(NOW()) AS ?nowDay)
  ?entity wdt:P569 ?date.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  FILTER(((MONTH(?date)) = ?nowMonth) && ((DAY(?date)) = ?nowDay))
}
LIMIT 10

Try it!

--Erfurth (talk) 13:21, 4 April 2017 (UTC)

Hi @Erfurth:, very interesting question. I think you have to work with round, but I haven't figured out yet how. Still trying. Q.Zanden questions? 15:28, 4 April 2017 (UTC)

Update: I still did not manage it, but I will continue, if others would like to try it, here is my code I got so far:

#Whose birthday is it today with a round number. e.g: 10, 20, 30 or 130 years
SELECT ?birthdayboy ?birthdayboyLabel (YEAR(?date) AS ?year) ?date ?decade ?decaderounded 
  WHERE {
  BIND(MONTH(NOW()) AS ?nowMonth)
  BIND(DAY(NOW()) AS ?nowDay)
  ?birthdayboy wdt:P569 ?date.
    BIND(((YEAR(NOW())-?year)/10) AS ?decade)
    BIND(ROUND((YEAR(NOW())-?year)/10) AS ?decaderounded)
    #Filter(?decade = ?decaderounded) #When I enable the filter it shows no matches at all.
  FILTER(((MONTH(?date)) = ?nowMonth) && ((DAY(?date)) = ?nowDay))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 10

Try it!

--Q.Zanden questions? 15:48, 4 April 2017 (UTC)

Finally got it. It shows a total of 610 results. Here the query:

#Whose birthday is it today with a round number. e.g: 10, 20, 30 or 130 years
SELECT ?birthdayboy ?birthdayboyLabel (YEAR(?date) AS ?year) ?date ?age WHERE {
  BIND(MONTH(NOW()) AS ?nowMonth)
  BIND(DAY(NOW()) AS ?nowDay)
  ?birthdayboy wdt:P569 ?date.
  BIND(YEAR(?date) AS ?year)
  BIND(((YEAR(NOW())) - ?year) / 10 AS ?decade)
  BIND(ROUND(((YEAR(NOW())) - ?year) / 10) AS ?decaderounded)
  BIND(?decade * 10 AS ?age)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  FILTER(?decade = ?decaderounded)
  FILTER(((MONTH(?date)) = ?nowMonth) && ((DAY(?date)) = ?nowDay))
}

Try it!

I'm sure it is possible to write this shorter, but I do not know how. Q.Zanden questions? 16:29, 4 April 2017 (UTC)

Thank you very much so far, but I'm searching still a solution. In the query for "Average lifespan by occupation" I found something like this, but don't know, how to integrate it to the above mentioned solution ...

#Average lifespan by occupation
        SELECT
        	?p
            ?occ
            (avg(year(?birth)) as ?birthYear)
            (avg(year(?death)) as ?deathYear)
        WHERE {
           ?p  wdt:P31 wd:Q5 ;
              wdt:P106 ?occ ;
              p:P569/psv:P569 [
                wikibase:timePrecision "9"^^xsd:integer ; # precision of at least year
                wikibase:timeValue ?birth ;
              ] ;
              p:P570/psv:P570 [
                wikibase:timePrecision "9"^^xsd:integer ; # precision of at least year
                wikibase:timeValue ?death ;
              ] .
        }
        GROUP BY ?p ?occ

Try it!

--Erfurth (talk) 09:43, 5 April 2017 (UTC)

Hi Erfurth, I am afraid that I do not understand your question properly, as your initial question was to look for people with a round birhtday today, but now you want another function to be implemented. What results do you want, and if I may ask: what would you like to do with it? I hope I can help further! Q.Zanden questions? 20:26, 10 April 2017 (UTC)

Query about Israeli nationals who died on 1970

I am interested in locating items regarding peaople with an Israeli nationality who died in 1970. Thanks. YoavR (talk) 15:34, 11 April 2017 (UTC)

SELECT ?item ?itemLabel (YEAR(?dod) as ?year_of_death)
WHERE
{
  ?item wdt:P27 wd:Q801;
        wdt:P570 ?dod.
  FILTER(YEAR(?dod) = 1970)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Shinnin (talk) 16:30, 11 April 2017 (UTC)

Getting info for category Daily Newspaper (Q1110794)

Hi can I ask here some help in writing a query? Actually I need to query Wikidata to get the title, description, all the "In more languages" items, all statements label, statement descriptions and links to some wikipedia languages pages for the category "Daily Newspaper"... Is this possible? Thanks for a feedback!

Screen 1

Screen 2

Screen 3

SELECT ?newspaper ?newspaperLabel ?countryLabel ?publisherLabel ?siteLabel WHERE {
  ?newspaper wdt:P31 wd:Q1110794.
  OPTIONAL { ?newspaper wdt:P495 ?country. }
  OPTIONAL { ?newspaper wdt:P123 ?publisher. }
  OPTIONAL { ?newspaper wdt:P856 ?site. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

This is what I could get in ten minutes work. Maybe you want some more information, please let me know. Q.Zanden questions? 11:04, 13 April 2017 (UTC)

@Mdeninno:, this is some more information. Do you still need more?

SELECT DISTINCT ?newspaperLabel ?newspaperDescription ?countryLabel ?publisherLabel ?site ?logo ?creationdate ?founder ?title ?popLabel ?lowLabel ?award WHERE {
  ?newspaper wdt:P31 wd:Q1110794.
  OPTIONAL { ?newspaper wdt:P495 ?country. }
  OPTIONAL { ?newspaper wdt:P123 ?publisher. }
  OPTIONAL { ?newspaper wdt:P856 ?site. }
  OPTIONAL { ?newspaper wdt:P154 ?logo. }
  OPTIONAL { ?newspaper wdt:P571 ?creationdate. }
  OPTIONAL { ?newspaper wdt:P112 ?founder. }
  OPTIONAL { ?newspaper wdt:P1476 ?title. }
  OPTIONAL { ?newspaper wdt:P291 ?pop. } #place of publication
  OPTIONAL { ?newspaper wdt:P364 ?low. } #language of original work
  OPTIONAL { ?newspaper wdt:P166 ?award. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }
}

Try it!

PS. please sign your message with four tildes (~~~~) Q.Zanden questions? 16:35, 13 April 2017 (UTC)

~~~~ @QZanden: This is good thanks a lot!

Last question: do you know how can I get the pointer to the en Wikipedia page for each record?

M

Here is it:

SELECT DISTINCT ?newspaperLabel ?newspaperDescription ?countryLabel ?publisherLabel ?site ?logo ?creationdate ?founder ?title ?popLabel ?lowLabel ?award ?article WHERE {
  ?newspaper wdt:P31 wd:Q1110794.
  OPTIONAL { ?newspaper wdt:P495 ?country. }
  OPTIONAL { ?newspaper wdt:P123 ?publisher. }
  OPTIONAL { ?newspaper wdt:P856 ?site. }
  OPTIONAL { ?newspaper wdt:P154 ?logo. }
  OPTIONAL { ?newspaper wdt:P571 ?creationdate. }
  OPTIONAL { ?newspaper wdt:P112 ?founder. }
  OPTIONAL { ?newspaper wdt:P1476 ?title. }
  OPTIONAL { ?newspaper wdt:P291 ?pop. } #place of publication
  OPTIONAL { ?newspaper wdt:P364 ?low. } #language of original work
  OPTIONAL { ?newspaper wdt:P166 ?award. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }
  Optional { ?article schema:about ?newspaper.
             ?article schema:isPartOf <https://en.wikipedia.org/>.}
}

Try it!

Q.Zanden questions? 23:50, 13 April 2017 (UTC)

~~~~ @QZanden: Fantastic!

I just tried to remove the other languages but still getting multiple istances for some records.. didn't understood why...

M

#Number of films by year and genre

There is already an example querry:

#Number of films by year and genre
#defaultView:ScatterChart
SELECT ?year (COUNT(?_genre) AS ?count) (SAMPLE(?_genreLabel) AS ?label) (?year AS ?year_shown) WHERE {
  ?item wdt:P31 wd:Q11424.
  ?item wdt:P577 ?_publication_date.
  ?item wdt:P136 ?_genre.
  ?_genre rdfs:label ?_genreLabel.
  BIND(STR(YEAR(?_publication_date)) AS ?year)
  FILTER((LANG(?_genreLabel)) = "en")
  FILTER(?_publication_date >= "2000-00-00T00:00:00Z"^^xsd:dateTime)
}
GROUP BY ?_genreLabel ?year
HAVING (?count > 30)

Try it!

I would like to have this Graph starting from 1900 and displayed as stacked lines/areas. --Zulu55 (talk) 09:50, 4 April 2017 (UTC)

Geotagged tajik Wikipedia map?

Tracemedia map of dewiki-plwiki

Hi. Tajik Wikipedia/tgwiki currently has 67,822 articles and 58,213 sitelinks on Wikidata (User:Pasleim/Sitelink_statistics). I would like a query that shows me a map of all Tajik Wikipedia articles that have coordinates and are sitelinked from wikidata. Something similar to the map here. (Bonus query: Is it possible to compare Tajik Wikipedia and Farsi Wikipedia/fawiki on a single map?) --Atlasowa (talk) 10:25, 12 April 2017 (UTC)

Similar to Wikidata:SPARQL_query_service/queries/examples#Human_settlements_that_are_not_described_in_the_English_Wikipedia? --Atlasowa (talk) 11:10, 12 April 2017 (UTC)

@Atlasowa:, here are the queries :

Only tgwiki :

#defaultView:Map
#sitelinks to tgwiki with coord
SELECT ?item ?sitelink ?coord WHERE {
	?item wdt:P625 ?coord .
	?sitelink schema:about ?item.
	?sitelink schema:isPartOf <https://tg.wikipedia.org/> .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

Here with both tgwiki and fawiki (caution, very heavy request, borderline timeout and when two coord are on the same point you only see one point, maybe with this query can be improved)

#defaultView:Map
SELECT ?item ?coord ?layer WHERE {
  {
    ?item wdt:P625 ?coord .
    ?sitelink schema:about ?item.
	?sitelink schema:isPartOf <https://tg.wikipedia.org/> .
	BIND("tgwiki" AS ?layer) .
  } UNION {
    ?item wdt:P625 ?coord .
    ?sitelink schema:about ?item.
	?sitelink schema:isPartOf <https://fa.wikipedia.org/> .
	BIND("fawiki" AS ?layer) .
  }
}

Try it!

Cdlt, VIGNERON (talk) 11:03, 15 April 2017 (UTC)

Merci beaucoup, VIGNERON! --Atlasowa (talk) 12:55, 15 April 2017 (UTC)

Query for prenames vs gender

I am looking for query which gives me the items with incorrectly added prenames in the sense that the assigned gender of the person's item and the assigned gender of the prename's item are not the same. As an example, I have just removed Christian (Q18001597) from Christian Serratos (Q235719) because assigning a male prename to a woman is obviously bullshit. Steak (talk) 19:14, 22 April 2017 (UTC)

SELECT ?item {
# ?item wdt:P21 wd:Q6581072; wdt:P735 [ wdt:P31 wd:Q12308941 ] . # female person with male given name
  ?item wdt:P21 wd:Q6581097; wdt:P735 [ wdt:P31 wd:Q11879590 ] . # male person with female given name
}

Try it!

You need to comment either of the two lines with # in the beginning, as shown for the female persons in this example. There are roughly ~4000+ results for both directions. —MisterSynergy (talk) 19:51, 22 April 2017 (UTC)

@Steak: why not to change Christian (Q18001597) to unisex given name (Q3409032) if male/female names spell the same? --Infovarius (talk) 21:41, 22 April 2017 (UTC)

This is of course also a possibilty, but it's not done with that because all descriptions also refer only to the male name. Steak (talk) 00:29, 23 April 2017 (UTC)

List of properties with less than 3 statements

Is it possible to create a query for properties that gives me all properties that have 3 statements or less? ChristianKl (talk) 21:17, 22 April 2017 (UTC)

SELECT ?prop ?propLabel ?st {
  ?prop a wikibase:Property; wikibase:statements ?st .
  FILTER( ?st <= 3 ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} ORDER BY DESC(?st)

Try it!

Matěj Suchánek (talk) 07:49, 23 April 2017 (UTC)

different items with same VIAF ID (P214)

Does anyone know why this query times out?

SELECT ?item1 ?item1Label ?viaf1 WHERE {
  ?item1 wdt:P214 ?viaf1.
  ?item2 wdt:P214 ?viaf2.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  FILTER(?item1 < ?item2)
  FILTER(?viaf1 = ?viaf2)
}
LIMIT 1

Try it!

Is there maybe another way to check if two different items have the same VIAF? Q.Zanden questions? 21:42, 22 April 2017 (UTC)

Here. Steak (talk) 07:39, 23 April 2017 (UTC)

SELECT ?item1 ?item1Label ?item2 ?item2Label ?viaf (URI(REPLACE(?formatter, '\\$1', ?viaf)) AS ?url) 
WITH
{
  SELECT * {
    ?item1 wdt:P214/^wdt:P214 ?item2 .
    FILTER( STR( ?item1 ) < STR( ?item2 ) ) .
  } LIMIT 1000
} AS %duplicates 
WITH
{
  SELECT * { wd:P214 wdt:P1630 ?formatter } LIMIT 1
} AS %formatter
WHERE
{
  INCLUDE %duplicates .
  INCLUDE %formatter .
  ?item1 wdt:P214 ?viaf .
  ?item2 wdt:P214 ?viaf .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

Matěj Suchánek (talk) 07:57, 23 April 2017 (UTC)

• Nice! (I inserted a few cr. )
  --- Jura 18:36, 23 April 2017 (UTC)

Thanks a lot, Matěj Suchánek! Does this query also works when I want to try it with a different external database like GND ID (P227)? Q.Zanden questions? 23:02, 23 April 2017 (UTC)

Just try it :)

SELECT ?item1 ?item1Label ?item2 ?item2Label ?GND (URI(REPLACE(?formatter, '\\$1', ?GND)) AS ?url) 
WITH
{
  SELECT * {
    ?item1 wdt:P227/^wdt:P227 ?item2 .
    FILTER( STR( ?item1 ) < STR( ?item2 ) ) .
  }
} AS %duplicates 
WITH
{
  SELECT * { wd:P227 wdt:P1630 ?formatter } LIMIT 1
} AS %formatter
WHERE
{
  INCLUDE %duplicates .
  INCLUDE %formatter .
  ?item1 wdt:P227 ?GND .
  ?item2 wdt:P227 ?GND .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

It does but there aren't many results. Matěj Suchánek (talk) 09:32, 24 April 2017 (UTC)

Is it possible to generate a list of Wikivoyage articles sorted by the amount of articles that exists for each destination in all wikivoyage editions?

? ויקיג'אנקי (talk) 18:19, 23 April 2017 (UTC)

• Wikidata:Wikivoyage/Lists/articles with badges might be close to what you look for.
  --- Jura 18:44, 23 April 2017 (UTC)

I need something much wider that would take into consideration everything that exists in all Wikivoyage editions and not only the articles classified as featured articles or good articles. whom created Wikidata:Wikivoyage/Lists/articles with badges ? (I only saw that a bot created and updates it) maybe the creator of that list would be able to generate such a list ? ויקיג'אנקי (talk) 20:47, 23 April 2017 (UTC)

SELECT ?item (count(*) as ?cnt) WHERE {
    ?article schema:about ?item;
             schema:isPartOf/wikibase:wikiGroup "wikivoyage".
} GROUP BY ?item ORDER BY DESC(?cnt)
LIMIT 1000

Try it!

--Pasleim (talk) 11:58, 28 April 2017 (UTC)

items with multiple genders

--Bigbossfarin (talk) 12:44, 27 April 2017 (UTC)

See Wikidata:Database_reports/Constraint_violations/P21#"Single value" violations. Steak (talk) 15:06, 27 April 2017 (UTC)

Threefold Query

I am interested in locating items regarding people with an American nationality + on which an article exists on hewiki + who died on 1970. Thanks. YoavR (talk)

To make it clear, what property do you refer to with nationality? ethnic group (P172) or country of citizenship (P27)? Matěj Suchánek (talk) 07:20, 28 April 2017 (UTC)

country of citizenship (P27). YoavR (talk) 09:05, 28 April 2017 (UTC)

SELECT ?item ?itemLabel WHERE {
	?item wdt:P27 wd:Q30 .
    ?article schema:about ?item ;
	         schema:isPartOf <https://he.wikipedia.org/> .
    ?item wdt:P570 ?dod .
    FILTER(YEAR(?dod) = 1970)
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

--Pasleim (talk) 11:45, 28 April 2017 (UTC)

Thank you very much. YoavR (talk) 12:06, 28 April 2017 (UTC)

Redundant subcategories

How can I get a list of items which have P31 software (Q7397) and some of it subclasses? Infovarius (talk) 10:18, 13 April 2017 (UTC)

SELECT DISTINCT ?item {
  ?item wdt:P31 wd:Q7397; wdt:P31/wdt:P279+ wd:Q7397 .
}

Try it!

Matěj Suchánek (talk) 15:09, 21 April 2017 (UTC)

Thanks! It seems working so I run removal of 4341 redundant main class (Q7397 itself). --Infovarius (talk) 21:39, 22 April 2017 (UTC)

I suggest to add this as Complex constraint at subclass of (P279) or at instance of (P31) d1g (talk) 01:44, 29 April 2017 (UTC)

Example with horses/optionals

Optionals can affect total number of results:

SELECT ?book ?title ?illustratorLabel ?publisherLabel ?published
WHERE
{
             ?book  wdt:P50   wd:Q35610.      # 138
  OPTIONAL { ?book  wdt:P1476 ?title.       } # 2 Q25713842
  OPTIONAL { ?book  wdt:P110  ?illustrator. } # 0
  OPTIONAL { ?book  wdt:P123  ?publisher.   } # 2 Q2297190
  OPTIONAL { ?book  wdt:P577  ?published.   } # 0
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

I'm not sure how to introduce it at Wikidata:A beginner-friendly course for SPARQL or what code is "correct". d1g (talk) 12:25, 28 April 2017 (UTC)

It's not really different if you just had:

SELECT * {
?book  wdt:P50   wd:Q35610.      # 73
?book  wdt:P1476 ?title.         # 2 Q25713842
}

Try it!

compared to:

SELECT DISTINCT ?book {
?book  wdt:P50   wd:Q35610.      # 73
?book  wdt:P1476 []         # 2 
}

Try it!

--- Jura 06:34, 30 April 2017 (UTC)

Intend wasn't to ask P50 and P1476 as required properties; or I don't understand suggestion yet.

Idea is to fetch at least 138 books, not less d1g (talk) 07:05, 30 April 2017 (UTC)