Wikidata

Wikidata:Request a query/Archive/2020/01

< Wikidata:Request a query‎ | Archive‎ | 2020
This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Exclude a list of QIDs

I have a worklist of potential Wikipedia article topics and I want to remove entries that lack enough sourcing to warrant an article (so that a newcomer won't see this list and try to create the article). My understanding is that manual exclusion from the query is my only option—is this correct? If so, what's the best way to write this such that it's easy to maintain a list of QIDs to exclude from a series of queries? MINUS or NOT EXISTS? When I edit the list of QIDs, is there a way to host the list in one location or would I need to update it in each query? czar 05:58, 1 January 2020 (UTC)

@Czar: There's also NOT IN, which is deployed within FILTER, in the form filter (?item NOT IN (wd:Q516882,wd:Q12345)). I suspect this might be the easiest approach. --Tagishsimon (talk) 14:03, 1 January 2020 (UTC)

Edits made by a user

Hi all,

I don't know if this request is possible or not: I would like to have the list of items (subclasses of structure of worship (Q1370598) in France (Q142)) for which the user Peter17-Bot add the property coordinate location (P625) (e.g. https://www.wikidata.org/w/index.php?title=Q38434124&diff=615373953&oldid=551868164). Thank you very much. Tubezlob (🙋) 11:29, 27 December 2019 (UTC)

@Tubezlob: AFAIK, not via SPARQL. Quarry probably can - here's a not very dissimilar query - https://quarry.wmflabs.org/query/16335 - presuming that Quarry can filter on the edit summary. (iirc, Quarry cannot see the content of edits, but can see edit metadata.) Sadly successful Quarrying continues to be beyond my paygrade. --Tagishsimon (talk) 06:03, 28 December 2019 (UTC)
@Tagishsimon: Thank you, that's a good hint! Is there an expert of Quarry here? 😊 Tubezlob (🙋) 09:01, 28 December 2019 (UTC)
If the edits happend in a relatively short period, I would try SPARQL. Have a look at Wikidata:SPARQL_query_service/queries/examples/advanced#Map_of_newspapers_in_the_United_States_by_presence_of_Infobox_newspaper_in_their_English_Wikipedia_article. I would combine:
Multichill (talk) 12:48, 2 January 2020 (UTC)

@Multichill: Thank you for your answer!

  • I made this SPARQL query:
SELECT ?church ?churchLabel ?location
WHERE
{
  ?church wdt:P31/wdt:P279* wd:Q1370598; 
         wdt:P17 wd:Q142;
         wdt:P625 ?location.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
Try it!
  • For the Mediawiki API, I tried this request (URL):
{
	"action": "query",
	"format": "json",
	"list": "usercontribs",
	"uclimit": "max",
	"ucstart": "2017-12-30T15:00:00.000Z",
	"ucend": "2017-12-31T15:00:00.000Z",
	"ucuser": "Peter17-Bot"
}

But I don't get anything. I don't understand how Mediawiki API works (I have no knowledge at all about APIs). How can I have just the list of QIDs? Also, I saw that this query is limited to 500 results. Is it possible to get directly the ≈ 7,500 results?

Thank you very much, Tubezlob (🙋) 16:23, 2 January 2020 (UTC)

I consulted the manual at mw:Wikidata_Query_Service/User_Manual/MWAPI#Supported_services and it only supports so called generators. That's possible for search, but not for usercontributions I'm afraid. Getting this data will involve some better trickery. Multichill (talk) 17:19, 2 January 2020 (UTC)
@Multichill: Thank you. So I did the work manually: I copied the modifs summaries obtained with https://www.wikidata.org/w/index.php?title=Special:Contributions&target=Peter17-Bot (≈ 15 pages of 500 edits) between 2017-12-30 16:31 and 2017-12-31 14:09 and did a regex search to get the QIDs. It was not so long and it works! Tubezlob (🙋) 18:29, 2 January 2020 (UTC)

Adding labels to aggregated values

I wanted to know the most common occupations of people with biographies at dawiki, and wrote this query: 

# Count biographies at dawiki by occupation 
SELECT ?job ?jobLabel (COUNT(?link) AS ?count)
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P106 ?job.
  ?link schema:about ?person;
        schema:isPartOf <https://da.wikipedia.org/>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" }
}
GROUP BY ?job ?jobLabel
ORDER BY DESC(?count)
limit 10
Try it!

The query times out. However it runs fine if I remove "?jobLabel" from SELECT and GROUP BY: 

# Count biographies at dawiki by occupation 
SELECT ?job (COUNT(?link) AS ?count)
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P106 ?job.
  ?link schema:about ?person;
        schema:isPartOf <https://da.wikipedia.org/>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" }
}
GROUP BY ?job
ORDER BY DESC(?count)
limit 10
Try it!

It cannot take long to find 10 labels after the counting is done, so can the query be rewritten to include the labels more efficiently? --Dipsacus fullonum (talk) 14:17, 2 January 2020 (UTC)

@Dipsacus fullonum: Named subqueries are your friend.
# Count biographies at dawiki by occupation 
SELECT ?job ?jobLabel ?count with {
SELECT ?job (COUNT(?link) AS ?count)
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P106 ?job.
  ?link schema:about ?person;
        schema:isPartOf <https://da.wikipedia.org/>.
} 
  GROUP BY ?job
  ORDER BY DESC(?count)
  limit 10
} as %i where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" }
}
Try it!
--Tagishsimon (talk) 14:51, 2 January 2020 (UTC)
@Tagishsimon: Thank you very much.
I also want to know the distribution of sexes for each occupation. I found a method defining variables ?male og ?female as either 0 or 1 and then making sums of these variables for each occupation:
# Count biographies at dawiki by occupation and sex
SELECT ?job ?jobLabel ?m ?f ?count with {
SELECT ?job (COUNT(?link) AS ?count) (SUM (?male) as ?m) (SUM (?female) as ?f)
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P106 ?job.
  OPTIONAL { ?person wdt:P21 ?sex. }
  BIND(IF(?sex = wd:Q6581097, 1, 0) as ?male) 
  BIND(IF(?sex = wd:Q6581072, 1, 0) as ?female)
  ?link schema:about ?person;
        schema:isPartOf <https://da.wikipedia.org/>.
} 
  GROUP BY ?job
  ORDER BY DESC(?count)
  limit 10
} as %i where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" }
}
Try it!

It works, but it seems a little weird to use sums. I wonder if there is a more direct and faster way to do this. --Dipsacus fullonum (talk) 16:34, 2 January 2020 (UTC)

Url and date

Hello. I want to find all items that have a reference with reference URL (P854) -> http://www.mof.gov.cy/mof/cystat/statistics.nsf/All/A2A7A50A241BC682C2257DBF002697D0/$file/GEO_CODES-2015.xls?OpenElement and publication date (P577) with any value. Xaris333 (talk) 01:09, 31 December 2019 (UTC)

@Xaris333: 
SELECT DISTINCT ?item ?itemLabel {
  ?item ?prop ?statement.
  ?statement prov:wasDerivedFrom ?ref.
  ?ref pr:P854 <http://www.mof.gov.cy/mof/cystat/statistics.nsf/All/A2A7A50A241BC682C2257DBF002697D0/$file/GEO_CODES-2015.xls?OpenElement>.
  ?ref pr:P577 [].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Vahurzpu (talk) 03:25, 3 January 2020 (UTC)

Thanks!! Xaris333 (talk) 22:35, 3 January 2020 (UTC)

News article archives

I'm looking to get a list of news article (Q5707594) with full work available at URL (P953) but no archive URL (P1065) qualifier (Q80317512 would be an example of that). --Trade (talk) 00:35, 1 January 2020 (UTC)

@Trade: Seems to be:
SELECT ?item ?itemLabel ?P953
WHERE 
{
  ?item wdt:P31 wd:Q5707594. 
  ?item p:P953 ?stat. 
  ?stat ps:P953 ?P953. 
  filter not exists {?stat prov:wasDerivedFrom/pr:P1065 ?thing .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
}
Try it!
--Tagishsimon (talk) 14:08, 1 January 2020 (UTC)
@Tagishsimon:, there seems to be something wrong? I've already found several items that already contains an archive URL (P1065) qualifier. --Trade (talk) 01:03, 2 January 2020 (UTC)
@Trade: Could you point me at one; might help in figuring out the error. --Tagishsimon (talk) 01:18, 2 January 2020 (UTC)
@Tagishsimon:, Mange mennesker dræbt i skyderier i moskeer (Q62071687) --Trade (talk) 09:59, 2 January 2020 (UTC)
@Trade: The problem here is that for 200 objects, the one you give as an exampel is one of them, the archive URL (P1065) is given as a qualifier instead of a reference in the full work available at URL (P953) claim. Only 15 objects use archive URL (P1065) as a reference.
If you want to exclude both types, try the following query, but I really think there is some cleaning up to be done for those objects that use archive URL (P1065) in an incorrect way. I am not sure which way is the correct, but the different uses are really confusing.
SELECT ?item ?itemLabel ?P953
WHERE 
{
  ?item wdt:P31 wd:Q5707594. 
  ?item p:P953 ?stat. 
  ?stat ps:P953 ?P953. 
  filter ( not exists {?stat prov:wasDerivedFrom/pr:P1065 ?thing .}  && not exists {?stat pq:P1065 ?thing2 } )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 11:28, 2 January 2020 (UTC)
New year's resolution is to read the question properly; Trade pointed to qualifiers. My response (and I was probably thinking of Xaris33's preceding question) dealt with references. --Tagishsimon (talk) 12:26, 2 January 2020 (UTC)
@Larske:, most of the incorrect uses were made a long time ago by a few users so correcting them should not be a problem. The way i understand there are two ways to use archive url: Either as a qualifier for full work available at URL (P953), official website (P856) and withdrawn identifier value (Q21441764). Or as a property for references. --Trade (talk) 15:29, 3 January 2020 (UTC)

Sitelinks for multiple items

At https://www.wikidata.org/w/index.php?title=Wikidata:Project_chat&oldid=1083113466#3_conflicted_categories_should_probably_be_reduced_to_2 it was suggested that three items should be made into two because of overlap in content on some wikis. I thought I could use the query service for a list of sitelinks which existed for all three. I used a query for URLs of items, but couldn't see a way to make a list of sites for each item or how many of these items existed on each site, so I used another query for a list of sites and their URLs, and copied the results into a spreadsheet to obtain the answer. Can this be achieved with SPARQL? Peter James (talk) 13:15, 25 December 2019 (UTC)

@Peter James: Not sure if I understand what you expect. Maybe some of the queries below takes you a bit forward:
SELECT ?item ?itemLabel ?sitelinks {
  VALUES ?item { wd:Q5645580 wd:Q6010682 wd:Q9703849 }
  ?item wikibase:sitelinks ?sitelinks .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! 
SELECT ?item ?itemLabel ?family (COUNT(?site) AS ?count) (GROUP_CONCAT(?sitey;SEPARATOR=', ') AS ?sites) {
  VALUES ?item { wd:Q5645580 wd:Q6010682 wd:Q9703849 }
  ?site schema:about ?item . 
  BIND(STRAFTER(STRBEFORE(STR(?site),'.org'),'//') AS ?sitex)
  BIND(STRAFTER(?sitex,'.') AS ?family)
  BIND(STRBEFORE(?sitex,'.') AS ?sitey)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?sitelinks ?family
ORDER BY ?itemLabel ?family
Try it!
@Larske: concerns the query right above: if there exists no sitelink, the item will not been listed. Am I right? Is it possible to change the query that items without any sitelink will also be listed? Doc Taxon (talk) 22:36, 5 January 2020 (UTC)
SELECT ?s ?categories (COUNT(?site1a) AS ?count) (GROUP_CONCAT(?site1b;SEPARATOR=', ') AS ?sites) WITH {
SELECT ?site1a (COUNT(*) AS ?count) (SUM(?x) AS ?s) {
  { ?site1 schema:about wd:Q5645580 . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(1 AS ?x) }
  UNION
  { ?site1 schema:about wd:Q6010682 . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(10 AS ?x) }
  UNION
  { ?site1 schema:about wd:Q9703849  . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(100 AS ?x) }
}
GROUP BY ?site1a
ORDER BY ?site1a
} AS %i WHERE {
  include %i
  BIND(STRAFTER(?site1a,'//') AS ?site1b)
  BIND(IF(?s=111,'All three categories',IF(?s=110,'All but Q5645580',IF(?s=101,'All but Q6010682',IF(?s=11,'All but Q9703849',IF(?s=1,'Only Q5645580',IF(?s=10,'Only Q6010682',IF(?s=100,'Only Q9703849',''))))))) AS ?categories)
}
GROUP BY ?s ?categories
ORDER BY DESC(?s)
Try it! 
SELECT ?site1b ?categories WITH {
SELECT ?site1a (COUNT(*) AS ?count) (SUM(?x) AS ?s) {
  { ?site1 schema:about wd:Q5645580 . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(1 AS ?x) }
  UNION
  { ?site1 schema:about wd:Q6010682 . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(10 AS ?x) }
  UNION
  { ?site1 schema:about wd:Q9703849  . BIND(STRBEFORE(STR(?site1),'.org') AS ?site1a) BIND(100 AS ?x) }
}
GROUP BY ?site1a
ORDER BY ?site1a
} AS %i WHERE {
  include %i
  BIND(STRAFTER(?site1a,'//') AS ?site1b)
  BIND(IF(?s=111,'All three categories',IF(?s=110,'All but Q5645580',IF(?s=101,'All but Q6010682',IF(?s=11,'All but Q9703849',IF(?s=1,'Only Q5645580',IF(?s=10,'Only Q6010682',IF(?s=100,'Only Q9703849',''))))))) AS ?categories)
}
ORDER BY ?site1b
Try it!
--Larske (talk) 13:04, 26 December 2019 (UTC)
Thanks; I was thinking of something like the fourth query, but the third query provides the best results. Peter James (talk) 14:51, 26 December 2019 (UTC)

I want to list the sitelink to English and German Wikipedia

SELECT ?item ?itemLabel ?sitelink_de ?sitelink_en ?ATP_Kennung (GROUP_CONCAT(?sitey;SEPARATOR=' ') AS ?sites) {
  OPTIONAL { ?item wdt:P536 ?ATP_Kennung. }
  ?site schema:about ?item . 
  BIND(STRAFTER(STRBEFORE(STR(?site),'.org'),'//') AS ?sitex)
  BIND(STRBEFORE(?sitex,'.') AS ?sitey)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
GROUP BY ?item ?itemLabel ?sitelink_de ?sitelink_en ?ATP_Kennung ?sitelinks
Try it!

Hi! I want to list the sitelink to English and to German Wikipedia, but the columns are empty. I only need the plain lemma like "Rafael Nadal". What can I do? Doc Taxon (talk) 23:09, 5 January 2020 (UTC)

?article doesn't work: 

SELECT ?item ?itemLabel ?sitelink_en ?sitelink_de ?ATP_Kennung (GROUP_CONCAT(?sitey;SEPARATOR=' ') AS ?sites) {
  OPTIONAL { ?item wdt:P536 ?ATP_Kennung. }
  ?article schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> ;  schema:name ?sitelink_en .
  ?article schema:about ?item ; schema:isPartOf <https://de.wikipedia.org/> ;  schema:name ?sitelink_de .
  ?site schema:about ?item . 
  BIND(STRAFTER(STRBEFORE(STR(?site),'.org'),'//') AS ?sitex)
  BIND(STRBEFORE(?sitex,'.') AS ?sitey)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
GROUP BY ?item ?itemLabel ?sitelink_en ?sitelink_de ?ATP_Kennung ?sitelinks
Try it!

Doc Taxon (talk) 23:19, 5 January 2020 (UTC)

@Doc Taxon: This, I think
SELECT ?item ?itemLabel ?sitelink_de ?sitelink_en ?ATP_Kennung ?sites with {
SELECT ?item ?ATP_Kennung (GROUP_CONCAT(?sitey;SEPARATOR=' ') AS ?sites) where {
  OPTIONAL { ?item wdt:P536 ?ATP_Kennung. }
  ?site schema:about ?item .   
  BIND(STRAFTER(STRBEFORE(STR(?site),'.org'),'//') AS ?sitex)
  BIND(STRBEFORE(?sitex,'.') AS ?sitey)
} GROUP BY ?item ?ATP_Kennung } as %i where
{ include %i
  optional {?sitelink_en ^schema:name ?article_en .
            ?article_en schema:about ?item ;
                     schema:isPartOf <https://en.wikipedia.org/> .}
  optional {?sitelink_de ^schema:name ?article_de .
            ?article_de schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
Try it!
--Tagishsimon (talk) 23:22, 5 January 2020 (UTC)
SELECT ?item ?itemLabel ?sitelink_de ?sitelink_en ?ATP_Kennung with {
SELECT ?item ?ATP_Kennung where {
  OPTIONAL { ?item wdt:P536 ?ATP_Kennung. }
  ?site schema:about ?item .   
} GROUP BY ?item ?ATP_Kennung } as %i where
{ include %i
  optional {?sitelink_en ^schema:name ?article_en .
            ?article_en schema:about ?item ;
                     schema:isPartOf <https://en.wikipedia.org/> .}
  optional {?sitelink_de ^schema:name ?article_de .
            ?article_de schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
Try it!

@Tagishsimon: okay, I don't need the list of ?sites and have taken them out. But I get 3829 results, in Wikidata there are 3858 items with P536. Where are the missing ones? Doc Taxon (talk) 23:34, 5 January 2020 (UTC)

@Doc Taxon: If you don't need ?sites, it becomes a simpler query. The lost items in the previous report would have been excluded for having no sitelinks, I think.
SELECT ?item ?itemLabel ?sitelink_de ?sitelink_en ?ATP_Kennung where {
  ?item wdt:P536 ?ATP_Kennung. 
  optional {?sitelink_en ^schema:name ?article_en .
            ?article_en schema:about ?item ;
                     schema:isPartOf <https://en.wikipedia.org/> .}
  optional {?sitelink_de ^schema:name ?article_de .
            ?article_de schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
Try it!
--Tagishsimon (talk) 23:39, 5 January 2020 (UTC)
@Tagishsimon: with this query I get all the 3858 results. Thank you very much, Doc Taxon (talk) 23:44, 5 January 2020 (UTC)
@Tagishsimon: is it possible to get also a column of ?itemLabel_de and ?itemLabel_en ? Doc Taxon (talk) 00:22, 6 January 2020 (UTC)
@Doc Taxon: Yup.
SELECT ?item ?ATP_Kennung ?itemLabel_en ?sitelink_en ?itemLabel_de ?sitelink_de where {
  ?item wdt:P536 ?ATP_Kennung. 
  optional {?sitelink_en ^schema:name ?article_en .
            ?article_en schema:about ?item ;
                     schema:isPartOf <https://en.wikipedia.org/> .}
  optional {?sitelink_de ^schema:name ?article_de .
            ?article_de schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}
  optional {?item rdfs:label ?itemLabel_en. filter(lang(?itemLabel_en)="en")}
  optional {?item rdfs:label ?itemLabel_de. filter(lang(?itemLabel_de)="de")}
}
Try it!
--Tagishsimon (talk) 00:30, 6 January 2020 (UTC)
cool, thank you Doc Taxon (talk) 00:34, 6 January 2020 (UTC)

Search for multiple (specific) types via wikibase:api "EntitySearch" running into 50 record limit?

I'm trying to let people search for anything, as long as it's something in the popular culture like an actor, book, movie, sports team, etc. So if I search "Patriots" I should get the NFL Team and the movie The Patriot. And I think I've gotten a good start with this: 

SELECT distinct ?ordinal ?item ?itemLabel ?itemDescription ?image WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "EntitySearch";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:search "Patriots";
                    mwapi:limit 1000;
                    mwapi:language "en" .
    ?item wikibase:apiOutputItem mwapi:item .
    ?ordinal wikibase:apiOrdinal true .
  } 
  ?item wdt:P31/wdt:P279* ?type.
  OPTIONAL{?item wdt:P18 ?image .}
  FILTER( ?type in (wd:Q5, wd:Q17537576, wd:Q12973014))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ASC (?ordinal) LIMIT 100
Try it!

I'm using the EntitySearch service so that I can get the :apiOrdinal value to help sort the results for relevance, and ?type contains the whole list of parent types so that I can just use "creative work" and "sports team" as catch-alls to filter on. (The FILTER list of Q types is incomplete, I was just starting to get the test going)

But, the problem happens when you replace "Patriots" with "Fellowship". In theory I should get the book and film Fellowship of the Ring. But the only result is for the "group" Fellowship of the Ring. If you take off the FILTER, you'll see it only returns 40 records from the EntitySearch for some reason? And if you change it to "Fellowship of the Ring" it will correctly find the book and film, so the EntitySearch can find those two. But for some reason it seems like EntitySearch is capping the results to a limit of 40?

Any ideas on what's going on? Or an alternative to do this search differently, preferably still with the :apiOrdinal so that the results are sorted by relevance?

Thanks!  – The preceding unsigned comment was added by Thomas.lumen (talk • contribs) at 17:16‎, 6. 7. 2019 (UTC).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)

Get all items that come under a particular category

I'm trying to find all items that can be marked as scientific items. For instance, photosynthesis, ohm's law, etc. Like, photosynthesis belongs to Biological Processes category but when I use the category in the below query I also get "death" as an item which is not an intended item. I think I am leaning more towards the concepts that one studies during his school years. Please let me know if there's a specific category that I should aim for? 

SELECT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31 wd:Q336.
}
LIMIT 200

 – The preceding unsigned comment was added by 204.246.162.35 (talk • contribs) at 22:35‎, 16 July 2019 (UTC).

Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


WikiProject every politician

Could you please help to create a query which returns all politicians and the linked persons to them (like sibling, siblings-in-law, family, unusual family relationships etc.)? Can this be done with one query?  – The preceding unsigned comment was added by Danail Stoyanov (talk • contribs) at 13 août 2019 à 09:15‎ (EST) (UTC).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


WikiData SPARQL query for plants data

I am a BE CSE undergrad student currently working on collecting information about plants so I am trying to query about all the plants from the wikidata Query Engine but I couldn't find a way. Any help on this is appreciableλ  – The preceding unsigned comment was added by 14.139.161.242 (talk • contribs) at 14 août 2019 à 10:06‎ (EST) (UTC).

Does this help you get started? 

SELECT ?plant ?plantLabel
WHERE
{
         # any instance or subclass of plant
  ?plant wdt:P31/wdt:P279* wd:Q756
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
Try it!

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


Query of Species of Trees in different languages

I am an artists looking at the representation of species of trees in Wikidata, looking for a list that contains f.ex. Quercus Robur, Acacia_podalyriifolia etc.

It is interesting to see that this query gets all individual trees, not species, as a result: SELECT ?item ?itemLabel WHERE { 

 ?item wdt:P31 wd:Q10884. #is instance of tree
 ?item wdt:P17 wd:Q30 . # specific for US, uses other code than 'citizenship' of a place specified non-human

} 

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

Rather, I'm looking for a list like this: https://en.wikipedia.org/wiki/Category:Trees_of_Africa (without the location property)

I would like to see what kind of species of trees are represented in EN, FR, SP, NL. I started something like this, but as 'taxon' is a property with a wide variety of values, it gives me a time out. SELECT ?item ?itemLabel WHERE { 

 ?item wdt:P31 wd:Q16521;
   wdt:P105 wd:Q7432.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

On the Wiki Data page of f.ex. Quercus Robur, everything refers to Quercus, which leads me to Genus/Family/Order etc. It would be wonderful to get some help on this. Many thanks in advance!  – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)

chemical substances used in plastics

I would like to retrieve the CAS number of all chemical substances that are used in plastics.

This is what I built with the Query Helper, however it returns nothing. Any suggestions? 

#CAS number of all chemical substances that are used in plastics
   SELECT ?sitelink ?item ?CAS_Registry_Number WHERE {
   ?item wdt:P279 wd:Q79529.
   ?sitelink schema:about ?item.
   SERVICE wikibase:label { bd:serviceParam wikibase:language 
   "[AUTO_LANGUAGE],en". }
   ?item wdt:P366 wd:Q60082936.
   OPTIONAL { ?item wdt:P231 ?CAS_Registry_Number. }
}
ORDER BY (?itemLabel)|
Try it!

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


Trouble with nested town/state entities

There is a lighthouse (Q7084940) in Scituate (Q2415936), Massachusetts. Scituate is located within Plymouth County (Q54086). How come asking for lighthouses in Plymouth County doesn't show Scituate light? (other lighthouses do show however) This is the query I'm using:

SELECT DISTINCT ?item ?itemLabel ?coords ?image WHERE { ?item wdt:P31 wd:Q39715 ; 

             wdt:P131 wd:Q54086 ;
             wdt:P625 ?coords

OPTIONAL { ?item wdt:P18 ?image } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nb,nn,en,fi" } } ORDER BY ?itemLabel


Neverind. I figured it out. Just add an asterisk after P131.

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


Query to get Fotball Players from different countries by divistion and date of birth

Hi, I was wondering if that is possible. I have not been able to query anything sports related so I am wondering if it isn't available.

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


DisGeNET and mirbase

hello, we are 3 bioinformaticians. We are currently working on the correlation of 2 data bases : disGeNET(URI:http://rdf.disgenet.org/resource) and mirDB.The mirDB is a data base that provide miRNA associated with target gene. disGeNET is a data base that provide gene correlated to their disease. We want to make a query that can relate between the 2 data base and give us the following response : find every miRNA correlated to a gene correlated to a disease(for example:myoptahy). We found in the query example the following query that might be the base of that we want.

  1. variants that are associated with renal cell carcinoma
(for our example we want :# miRNA that are associated with myopathy(disease)).

SELECT DISTINCT ?reference ?referenceLabel ?pmid WHERE { ?item wdt:P3329 ?civicId ; 

         ?property  ?object .
   ?object prov:wasDerivedFrom ?provenance .
   ?provenance pr:P248 ?reference .
   ?reference wdt:P31 wd:Q13442814 ;
              wdt:P698 ?pmid .

SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } }

We didn't find the URI of mirDB. is it a problem ? Thank you for your attention. We will be pleased to read your response.


 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


Common properties that link politicians

Hi,

I am trying to get a list of common properties, ideally sorted in descending order, that link two items whose occupation is "politician."

These would be things like "father," "mother," "sibling" etc.

Thank you very much.

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


All eukaryotic proteins

Hi!

I've been trying to create a query which finds all proteins found in species belonging to the domain "eukaryote".

I started with this: 

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q8054;
        wdt:P703/?????.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!


But quickly ran in to the issue that there is no way to find out i a species is a eukaryote or not, hence the question marks.

Thanks!

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


Property paths with normalRank

I can't seem to be able to combine this answer for returning items with normalRank with property paths.

For example, how would I apply it to the following query for retuning all (categories of) cheeses. 

SELECT ?item ?itemLabel WHERE {
   ?item ((wdt:P31*)/(wdt:P279*)) wd:Q10943;
     rdfs:label ?itemLabel.
   FILTER((LANG(?itemLabel)) = "en")
 }
Try it!

The following doesn't seem to work, and neither do any combinations of it, as far as I've discovered. 

SELECT ?item ?itemLabel WHERE {
  ?item (((p:P31*)/(ps:P31*))/((p:P279*)/(ps:P279*))) wd:Q10943;
    rdfs:label ?itemLabel.
  FILTER((LANG(?itemLabel)) = "en")
 }
Try it!

 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Stale request. Random signature with random timestamp archving: Random signature 01:01, 1 January 2020 (UTC)


office held by head of government

Hello. Many items have located in the administrative territorial entity (P131) -> Paphos District (Q59133). Some of them have also statements with office held by head of government (P1313). Is it possible a query to list all these statements with office held by head of government (P1313)? Xaris333 (talk) 20:00, 6 January 2020 (UTC)

@Xaris333: Here's all of them, with a column for P1313. Remove optional{} to make it mandatory that the item has a P1313 ... use filternot exists {} if you want only those with no P131. I forget what the code for your preferred language is, so you might need to fix that in the wikibase:label service.
SELECT ?item ?itemLabel ?office ?officeLabel WHERE 
{
  ?item wdt:P131 wd:Q59133 .
  optional {?item wdt:P1313 ?office . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?officeLabel)
Try it!
hth --Tagishsimon (talk) 20:08, 6 January 2020 (UTC)

@Tagishsimon: Hello. Thanks. Is there other way to do this? I want to use the query for Wikidata list, so I want the results to be the Presidents of Communal Councils and not the communities. Xaris333 (talk) 20:24, 6 January 2020 (UTC)

@Xaris333: Sure; this, or, at least, this direction.
SELECT ?item ?itemLabel ?authority ?authorityLabel WHERE 
{
  ?authority wdt:P131 wd:Q59133 .
  ?authority wdt:P1313 ?item . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?itemLabel)
Try it!
--Tagishsimon (talk) 20:49, 6 January 2020 (UTC)

Thanks! Xaris333 (talk) 20:59, 6 January 2020 (UTC)

Leading zeros

SELECT ?item (CONCAT(?abbr, ':', ?seasonOrd, '⨉', ?episodeOrd) as ?code) WHERE {
  ?item wdt:P31 wd:Q61220733.
  MINUS { ?item wdt:P31 wd:Q21664088. }
  ?item wdt:P179 ?series.
  ?series wdt:P1813 ?abbr.
  ?item wdt:P4908 ?season.
  ?season p:P179 [ pq:P1545 ?seasonOrd ].
  ?item p:P4908 [ pq:P1545 ?episodeOrd ].
}
Try it!

The last number in the ?code (?episodeOrd) field should always have two digits. for instance DS9:1⨉5 → DS9:1⨉05. I wasn't able to find a PadLeft funtion. any idea how to do that? --Loominade (talk) 11:58, 6 January 2020 (UTC)

@Loominade: You have to roll your own function.
SELECT ?item (CONCAT(?abbr, ':', ?seasonOrd, '⨉', ?thing) as ?code) WHERE {
  ?item wdt:P31 wd:Q61220733.
  MINUS { ?item wdt:P31 wd:Q21664088. }
  ?item wdt:P179 ?series.
  ?series wdt:P1813 ?abbr.
  ?item wdt:P4908 ?season.
  ?season p:P179 [ pq:P1545 ?seasonOrd ].
  ?item p:P4908 [ pq:P1545 ?episodeOrd ].
  bind(if(strlen(?episodeOrd)=1,concat("0",?episodeOrd),?episodeOrd) as ?thing)
}
Try it!
--Tagishsimon (talk) 16:06, 6 January 2020 (UTC)

that worked, thank you 😀--Loominade (talk) 09:11, 7 January 2020 (UTC)

Select a point in time and a determination method

This query should return only items with a spesific point in time and determination method but it doesn't. There should be only one row per municipality geographical code and one population data. 

SELECT ?code ?item ?itemLabel ?population ?date
WHERE  { 
    ?item wdt:P3856 ?code .
    ?item p:P1082 [ pq:P459 wd:Q29051383 ] .
    ?item p:P1082 [ pq:P585  ?date ] .
    FILTER (?date  >= "2020-01-01T00:00:00Z"^^xsd:dateTime )
    ?item wdt:P1082 ?population .
    FILTER NOT EXISTS { ?item p:P31 [ pq:P582 ?pqend ] }
    FILTER NOT EXISTS { ?item wdt:P582 ?enddt }
    FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt } 
    SERVICE wikibase:label {bd:serviceParam wikibase:language "en,fr" .}
 } 
order by ?code
Try it!

--Yanik B 14:09, 9 January 2020 (UTC)

@YanikB: Might this be what you seek? In your query, nothing particularly pulled together the three P1082 requirements so as to require that the returned population had an appropriate date & determination method.
SELECT ?code ?item ?itemLabel ?population ?date
WHERE  { 
    ?item wdt:P3856 ?code .
    ?item p:P1082 [ ps:P1082 ?population; pq:P459 wd:Q29051383; pq:P585  ?date  ] .
    FILTER (?date  >= "2020-01-01T00:00:00Z"^^xsd:dateTime )
    FILTER NOT EXISTS { ?item p:P31 [ pq:P582 ?pqend ] }
    FILTER NOT EXISTS { ?item wdt:P582 ?enddt }
    FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt } 
    SERVICE wikibase:label {bd:serviceParam wikibase:language "en,fr" .}
 } 
order by ?code
Try it!
--Tagishsimon (talk) 16:07, 9 January 2020 (UTC)
@Tagishsimon: That's exacly it. So nice, thx --Yanik B 16:17, 9 January 2020 (UTC)

Change query

Hello. Can anyone change this query to find all items that have a reference with reference URL (P854) -> http://www.cystat.gov.cy/mof/cystat/statistics.nsf/All/59681B67FE82FD39C2257AD90053F3FA/$file/POP_CEN_11-POP_PLACE_RESID-EL-171115.xls?OpenElement with also (at the same reference) title (P1476) -> Απογραφή πληθυσμού 2011 (Greek language) and title (P1476) -> Census of population 2011 (English language). 


SELECT DISTINCT ?item ?itemLabel {
  ?item ?prop ?statement.
  ?statement prov:wasDerivedFrom ?ref.
  ?ref pr:P854 <http://www.cystat.gov.cy/mof/cystat/statistics.nsf/All/59681B67FE82FD39C2257AD90053F3FA/$file/POP_CEN_11-POP_PLACE_RESID-EL-171115.xls?OpenElement>.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Xaris333 (talk) 13:58, 11 January 2020 (UTC)

@Xaris333: Here is a query proposal:
SELECT DISTINCT ?item ?itemLabel {
  ?item ?prop ?statement.
  ?statement prov:wasDerivedFrom ?ref.
  ?ref pr:P854 <http://www.cystat.gov.cy/mof/cystat/statistics.nsf/All/59681B67FE82FD39C2257AD90053F3FA/$file/POP_CEN_11-POP_PLACE_RESID-EL-171115.xls?OpenElement>.
  ?ref pr:P1476 'Απογραφή πληθυσμού 2011'@el .
  ?ref pr:P1476 'Census of population 2011'@en .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 15:49, 11 January 2020 (UTC)

Thanks!! Xaris333 (talk) 19:30, 11 January 2020 (UTC)

Order by number - correctly

Given a simple query like 

SELECT ?item ?itemLabel ?value
{
	?item wdt:P5492 ?value .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en"  }    
}
ORDER BY ?value
Try it!

is it possible, in some way, to order the values correctly by a number? At the moment after "1" I have "10015" ... "10587", "106", "10671" etc. Thank you very much, --Epìdosis 16:42, 12 January 2020 (UTC)

Your values are strings, thus it sorts alphanumerically. It works if you convert to a numeric format for numerical sorting: 
SELECT ?item ?itemLabel ?value
{
	?item wdt:P5492 ?value .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en"  }    
}
ORDER BY xsd:integer(?value)
Try it!
 --MisterSynergy (talk) 17:08, 12 January 2020 (UTC)

New year challenge

Hi and happy New year to everybody !

I have tried to combine these 2 queries to get the number and percentage of people dead over 60 year by blood type O, A, B and AB (without the others values), without succès. Someone can help ? 

#Longévité en fonction du groupe sanguin
SELECT distinct (count (?item) as ?count) ?groupeLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
   ?item wdt:P1853 ?groupe ;
     }
group by ?groupeLabel
Try it! 
#Longévité en fonction du groupe sanguin
SELECT distinct (count (?item) as ?count) ?groupeLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
   ?item wdt:P1853 ?groupe ;
         wdt:P569 ?naissance ;
         wdt:P570 ?mort .
        FILTER(?age > 60) .
        BIND((?mort - ?naissance)/365.2425 as ?age )
     }
group by ?groupeLabel
Try it!

Simon Villeneuve (talk) 15:15, 13 January 2020 (UTC)

So far, I got this. My problem is that the percentage is calculated with all the blood types. I want a % giving the proportion of each blood type (example : for O group, X % have died at 60, Y % between 70 and 80, etc.) 
SELECT ?age60 ?age70 ?age80 ?age90 ?items
(CONCAT(str(round(10000 * ?o/?items) / 100), "%")  as ?O)
(CONCAT(str(round(10000 * ?a/?items) / 100), "%")  as ?A)
(CONCAT(str(round(10000 * ?b/?items) / 100), "%")  as ?B)
(CONCAT(str(round(10000 * ?ab/?items) / 100), "%")  as ?AB) {
  { 
    SELECT ?age60 ?age70 ?age80 ?age90 (count(?o_items) as ?o) (count(?a_items) as ?a) (count(?b_items) as ?b) (count(?ab_items) as ?ab) (count(?all_items) as ?items) {
      {?o_items wdt:P1853 wd:Q19831451 ; wdt:P569 ?naissance ; wdt:P570 ?mort ; wdt:P31 wd:Q5 } UNION
      {?a_items wdt:P1853 wd:Q19831453 ; wdt:P569 ?naissance ; wdt:P570 ?mort ; wdt:P31 wd:Q5 } UNION
      {?b_items wdt:P1853 wd:Q19831454 ;wdt:P569 ?naissance ; wdt:P570 ?mort ; wdt:P31 wd:Q5 } UNION
      {?ab_items wdt:P1853 wd:Q19831455 ; wdt:P569 ?naissance ; wdt:P570 ?mort ; wdt:P31 wd:Q5 } UNION
      {?all_items wdt:P1853 ?group ; wdt:P569 ?naissance ; wdt:P570 ?mort ; wdt:P31 wd:Q5}
      BIND(round((?mort - ?naissance)/365.2425) as ?age )
      BIND ((60 < ?age && ?age < 70) as ?age60)
      BIND ((70 < ?age && ?age < 80) as ?age70)
      BIND ((80 < ?age && ?age < 90) as ?age80)
      BIND ((90 < ?age && ?age < 100) as ?age90)
  	}
  	GROUP BY ?age60 ?age70 ?age80 ?age90 
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en"  } 
}
Try it!
Simon Villeneuve (talk) 19:12, 14 January 2020 (UTC)

Map: Set individual colors for ordinal scale

The following query is counting the numbers of co-authors from a specific author identified by his/her orcid grouped by the co-authors country of citizenship: 

#defaultView:Map
SELECT  ?coauthor_countryLabel ?shape ?numOfCoAuthors ?layer ?rgb 
WITH {
  SELECT ?author ?searched_author WHERE {
    # Find co-authors
    ?searched_author wdt:P496  "0000-0002-6778-0887".
    ?work wdt:P50 ?searched_author, ?author .
  }
  GROUP BY ?author  ?searched_author
} AS %authors
WITH {
  SELECT ?shape ?coauthor_country (COUNT(?author) AS ?numOfCoAuthors) WHERE {
    INCLUDE %authors
           
    # Exclude self-links
    FILTER (?searched_author != ?author)
    
    ?author wdt:P27 ?coauthor_country .
    ?coauthor_country wdt:P3896 ?shape.


  }GROUP BY ?shape ?coauthor_country
} AS %result
WHERE {
  INCLUDE %result
  # Label the results 
  BIND(IF(?numOfCoAuthors <= 2,"< 2 Authors",
          IF(?numOfCoAuthors < 5,"< 5 Authors",
          IF(?numOfCoAuthors < 10,"< 10 Authors","> 10 Authors"))) AS ?layer).
 
  #Set ColorRange for Ordinal Scale
  BIND(IF(?numOfCoAuthors <= 2,"#fef0d9",
          IF(?numOfCoAuthors < 5,"#fdcc8a",
          IF(?numOfCoAuthors < 10,"#fc8d59","#d7301f"))) AS ?rgb).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
} ORDER BY DESC(?numOfCoAuthors)
Try it!

As i set up in the end of the query above, i want to define an individual color range, using BIND and IF Functions like to define the Layer-Property. I thought, that it's possible to define the color of points or shaps on wdqs-maps individually. Unfortunately in the query above it won't work. Any ideas? Thanks! --Mfchris84 (talk) 13:23, 14 January 2020 (UTC)

@User:Mfchris84: something like #defaultView:Map{layer:?rbg}, I think. --Tagishsimon (talk) 01:37, 15 January 2020 (UTC)
@Tagishsimon: that was my idea too, but nothing changed. but - i found the solution: the color-hex-values should be stored in ?rgb without # at the beginning: ?rgb and ?layer variables are fixed variables in the Map-view nd are responsible for re-design the color-set or create the layer.
#defaultView:Map
SELECT  ?coauthor_countryLabel ?shape ?numOfCoAuthors ?rgb ?layer
WITH {
  SELECT ?author ?searched_author WHERE {
    # Find co-authors
    ?searched_author wdt:P496  "0000-0002-6778-0887".
    ?work wdt:P50 ?searched_author, ?author .
  }
  GROUP BY ?author  ?searched_author
} AS %authors
WITH {
  SELECT ?shape ?coauthor_country (COUNT(?author) AS ?numOfCoAuthors) WHERE {
    INCLUDE %authors
           
    # Exclude self-links
    FILTER (?searched_author != ?author)
    
    ?author wdt:P27 ?coauthor_country .
    ?coauthor_country wdt:P3896 ?shape.


  }GROUP BY ?shape ?coauthor_country
} AS %result
WHERE {
  INCLUDE %result
  # Label the results 
  BIND(IF(?numOfCoAuthors <= 2,"< 2 Authors",
          IF(?numOfCoAuthors < 5,"< 5 Authors",
          IF(?numOfCoAuthors < 10,"< 10 Authors","> 10 Authors"))) AS ?layer).
 
  #Set ColorRange for Ordinal Scale
  BIND(IF(?numOfCoAuthors <= 2,"fef0d9",
          IF(?numOfCoAuthors < 5,"fdcc8a",
          IF(?numOfCoAuthors < 10,"fc8d59","d7301f"))) AS ?rgb).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
} ORDER BY DESC(?numOfCoAuthors)
Try it!

Liste des codes Wikidata des communes françaises

Bonjour et bonne année tous,
La requête suivante est sensée lister l’ensemble les codes Wikidata des communes françaises au 01/01/2019. 

SELECT ?insee (REPLACE(STR(?item),".*Q","Q") AS ?id)
WHERE {
  BIND ( "2019-01-02"^^xsd:dateTime as ?dateQuery )
  ?item p:P31 ?nature.
  { ?nature ps:P31 wd:Q484170. }                           # commune de France
  UNION { ?nature ps:P31 wd:Q2989454. }                    # commune nouvelle
  UNION { ?nature ps:P31 wd:Q22927616. }                   # commune française à statut particulier
  OPTIONAL { ?nature pq:P582 ?dateFin }
  OPTIONAL { ?item wdt:P576 ?dateFin. }                    # date dissolution
  FILTER ( !BOUND(?dateFin)|| ?dateFin > ?dateQuery )
  OPTIONAL { ?nature pq:P580 ?dateDébut }
  OPTIONAL { ?item wdt:P571 ?dateDébut. }                  # date création
  FILTER ( !BOUND(?dateDébut) || ?dateDébut < ?dateQuery )
  FILTER ( !STRSTARTS(?insee, "98") )                      # - Polynésie
  FILTER ( !STRSTARTS(?insee, "975") )                     # - Saint-Pierre et Miquelon
  ?item wdt:P374 ?insee.
}
ORDER BY (?insee)
Try it!

Le pb est que j’obtiens le message d’erreur suivant « Limite du temps de requête atteinte ».

Je me dis qu’en limitant la liste aux communes d’une région, avec un filtre, le temps doit être moins long. Quelqu’un pourrait-il compléter le code pour restreindre la requête à par exemple l’Occitanie : P31 Q3674 (région) Q18678265 (Occitanie).

Merci par avance

In english : Hi. I have a problem with the above request. I get the following error message "Request time limit reached".

I tell myself that by limiting the list to the municipalities of a region, with a filter, the time should be shorter. Could someone complete the code to restrict the query to, for example, Occitania: P31 Q3674 (region) Q18678265 (Occitanie)

Thanks. Roland45 (talk) 08:40, 14 January 2020 (UTC)

Bonjour à tous. Finalement, j'ai résolu le pb, la première ligne de la requête comportait une erreur. en remplaçant SELECT ?insee (REPLACE(STR(?item),".*Q","Q") AS ?id par simplement SELECT ?insee ?item, cela fonctionne Roland45 (talk) 15:28, 15 January 2020 (UTC)

Wikidata:Database reports/items with P569 greater than P570

Can someone look at this report and query and find out why it stopped running when the decade changed? --RAN (talk) 15:55, 14 January 2020 (UTC)

The query simply timed out. I tweaked it a bit and the report got an update, but it is still close to the timeout limit. —MisterSynergy (talk) 19:47, 14 January 2020 (UTC)
Is there something we can do to prevent the timeout? --RAN (talk) 01:20, 15 January 2020 (UTC)
Not really. Internally the query processes almost 2 million items from which it filters the few problematic ones. With the current timeout limit of 60 seconds it will always be a challenge to get this done in time, no matter which tweak we use to make the query more efficient. —MisterSynergy (talk) 07:38, 15 January 2020 (UTC)
  • @MisterSynergy: How about if we modify the query to only look at new entries and entries that have been modified since it was last run? I corrected the about 1K historical errors, so we should only have errors in new entries, and where people add errors on purpose, or where bots conflate people with similar names and add in a nonsensical additional birth or death date. RAN (talk) 00:40, 17 January 2020 (UTC)
    • Unfortunately we cannot query following that strategy, as WDQS has basically no access to item histories; it only sees the status quo of an item. The only way is to collect all ~2M items that have P569 and P570 and P31:Q5 and then filter those few which are probably incorrect. —MisterSynergy (talk) 00:49, 17 January 2020 (UTC)

all items with P536 or P597 and not P4544

Hi! How can I find all items having P536 OR P597 AND NOT P4544? Thank you for any help. Doc Taxon (talk) 03:27, 15 January 2020 (UTC) 

SELECT DISTINCT ?item WHERE { ?item wdt:P536|wdt:P597 [] . MINUS { ?item wdt:P4544 [] } }
Try it!
 —MisterSynergy (talk) 12:44, 15 January 2020 (UTC)

Unwanted instances

This request returns instances that doesn't belong to the specified instance (local administrative entity of quebec (Q81063407)). It should return only one instance for each 1280 items. 

SELECT DISTINCT ?item ?code ?itemLabel ?statutLabel ?population
WHERE { {  
       SELECT DISTINCT ?item ?code ?itemLabel ?statutLabel ?population
       WHERE {
         ?item wdt:P31/wdt:P279* wd:Q81063407 .
         ?item wdt:P31 ?statut .
         ?item p:P31 ?statut_statement .
         FILTER NOT EXISTS { ?statut_statement pq:P582 ?dtend . }
         ?item wdt:P3856 ?code . 
         ?item p:P1082 [ ps:P1082 ?population; pq:P459 wd:Q29051383; pq:P585  ?date  ] .
         FILTER (?date  >= "2019-01-01T00:00:00Z"^^xsd:dateTime )
         FILTER NOT EXISTS { ?item wdt:P582 ?enddt . }
         FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt . } 
         SERVICE wikibase:label { bd:serviceParam wikibase:language 'en,fr'. } .
     } }
 }
Try it!
This, probably. Some items have several P31s each of which are P279s of wd:Q81063407.
SELECT DISTINCT ?item ?code ?itemLabel (group_concat(?statutL;separator="; ") as ?statutLabel) ?population
       WHERE {
         ?item wdt:P31/wdt:P279* wd:Q81063407 .
         ?item wdt:P31 ?statut .
         ?item p:P31 ?statut_statement .
         FILTER NOT EXISTS { ?statut_statement pq:P582 ?dtend . }
         ?item wdt:P3856 ?code . 
         ?item p:P1082 [ ps:P1082 ?population; pq:P459 wd:Q29051383; pq:P585  ?date  ] .
         FILTER (?date  >= "2019-01-01T00:00:00Z"^^xsd:dateTime )
         FILTER NOT EXISTS { ?item wdt:P582 ?enddt . }
         FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt . } 
         SERVICE wikibase:label { bd:serviceParam wikibase:language 'en,fr'. 
                                 ?item rdfs:label ?itemLabel .
                                 ?statut rdfs:label ?statutL . } 
 } group by ?item ?code ?itemLabel ?population
Try it!
--Tagishsimon (talk) 12:26, 16 January 2020 (UTC)
@Tagishsimon: Nice try but there are statutL with values that doesnt belong to local administrative entity of quebec (Q81063407), big city (Q1549591) for example.
This request almost do the job except for enclave (Q171441) ??? 
SELECT DISTINCT ?item ?code ?itemLabel ?statutLabel ?population
  WHERE {
         ?item wdt:P31/wdt:P279* wd:Q81063407 .
         ?item wdt:P31 ?statut .
         ?item p:P31 ?statut_statement .
         FILTER NOT EXISTS { ?statut_statement pq:P582 ?dtend . }
         ?item wdt:P3856 ?code . 
         ?item p:P1082 [ ps:P1082 ?population; pq:P459 wd:Q29051383; pq:P585  ?date  ] .
         FILTER (?date  >= "2019-01-01T00:00:00Z"^^xsd:dateTime )
         FILTER NOT EXISTS { ?item wdt:P582 ?enddt . }
         FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt . } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q902814 } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q1637706 } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q188443 } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q200250 } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q1665321 } 
#         FILTER NOT EXISTS { ?item wdt:P31 wd:Q171441 } 
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q1549591 }
         FILTER NOT EXISTS { ?item wdt:P31 wd:Q3518817 }
         SERVICE wikibase:label { bd:serviceParam wikibase:language 'en,fr'. } .
 }
Try it!
--Yanik B 14:04, 16 January 2020 (UTC)
@YanikB: Yeah, sorry; I didn't look hard at what was going on. Now I do, too many P31 statements. How's this?
SELECT DISTINCT ?item ?code ?itemLabel ?statutLabel ?population
  WHERE {
         ?item p:P31 ?statut_statement .
         ?statut_statement ps:P31 ?statut .
         ?statut wdt:P279* wd:Q81063407 .   
         FILTER NOT EXISTS { ?statut_statement pq:P582 ?dtend . }
         ?item wdt:P3856 ?code . 
         ?item p:P1082 [ ps:P1082 ?population; pq:P459 wd:Q29051383; pq:P585  ?date  ] .
         FILTER (?date  >= "2019-01-01T00:00:00Z"^^xsd:dateTime )
         FILTER NOT EXISTS { ?item wdt:P582 ?enddt . }
         FILTER NOT EXISTS { ?item wdt:P576 ?dissoldt . } 
         SERVICE wikibase:label { bd:serviceParam wikibase:language 'en,fr'. } .
 }
Try it!
--Tagishsimon (talk) 22:31, 16 January 2020 (UTC)
@Tagishsimon: Very elegant, that's what I was looking for. Your're the best. ^.^ --Yanik B 01:48, 17 January 2020 (UTC)

Group URLs in one cell

Hi all! I have a doubt. Given this query, 

SELECT ?person ?personLabel (URI(CONCAT("http://www.treccani.it/enciclopedia/",?ei,"_(Enciclopedia-Italiana)")) AS ?eiUrl)
WHERE {
  ?person wdt:P4223 ?ei . 
  ?person wdt:P31 wd:Q5 .
  { ?person wdt:P27 wd:Q38 . } UNION { ?person wdt:P27 wd:Q172579 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" . }
}
Try it!

because of the fact that Treccani's Enciclopedia Italiana ID (P4223) has often more than one value, I would appreciate very much the possibility of making each person appear only in one line, with different eiUrls grouped in one cell, separated each other by ", ".

Eg. wd:Q13888 - Luchino Visconti - <http://www.treccani.it/enciclopedia/visconti-di-modrone-luchino_(Enciclopedia-Italiana)>, <http://www.treccani.it/enciclopedia/visconti-di-modrone-luchino_res-1732c2c9-87e7-11dc-8e9d-0016357eee51_(Enciclopedia-Italiana)>.

Is it possible? Thank you all, --Epìdosis 11:30, 14 January 2020 (UTC)

You can only have non-clickable URLs concatenated in one field, as e.g. here. There is no possibility to concatenate clickable URLs into one field in the WDQS interface; you’d need to do some postprocessing with a script to achieve that. —MisterSynergy (talk) 13:30, 14 January 2020 (UTC)
@MisterSynergy: I understand, thank you! --Epìdosis 13:32, 14 January 2020 (UTC)
@Epìdosis: you can make it clickable in Listeria using [ ]. --- Jura 14:20, 20 January 2020 (UTC)

All norwegians born before 1802

I would like to have a Query listening all country of citizenship (P27) Norway (Q20) born before 1802 but not dead before 1802 and their residence (P551). I would also like to have residence (P551) With point in time (P585) with References. Breg Pmt (talk) 21:20, 15 January 2020 (UTC)

There doesn't seem to be any entries with all that information so I put the residence data in an OPTIONAL clause. Popperipopp (talk) 11:08, 16 January 2020 (UTC)
SELECT ?person ?personLabel ?residence ?residenceLabel ?residencePointInTime ?reference
WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person wdt:P27 wd:Q20 .
  ?person wdt:P569 ?birth .
  ?person wdt:P570 ?death .
  
  OPTIONAL {
    ?person p:P551 ?residenceStatement .
    ?residenceStatement ps:P551 ?residence .
    
    OPTIONAL { ?residenceStatement pq:P585 ?residencePointInTime . }
    OPTIONAL { ?residenceStatement prov:wasDerivedFrom ?reference . }
  }
  
  FILTER (?birth < "1802-01-01"^^xsd:dateTime && ?death >= "1802-01-01"^^xsd:dateTime)
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
I fixed ending } and changed "<" to ">=" for ?death. That actually gives 1 person which all requirements: Baltazar Mathias Keilhau (Q893022) --Dipsacus fullonum (talk) 13:47, 20 January 2020 (UTC)

Dams

Hello. I want to find all items that have country (P17)->Cyprus (Q229) and a value with instance of (P31) which has instance of (P31)->dam (Q12323). Xaris333 (talk) 18:30, 16 January 2020 (UTC)

@Xaris333: Take a good hard look at how close your question is to the SPARQL you seek. What's stopping you :) --Tagishsimon (talk) 22:35, 16 January 2020 (UTC)
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P17 wd:Q229 . 
  ?item wdt:P31 wd:Q12323 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 22:35, 16 January 2020 (UTC)

@Tagishsimon: Hello. Is not the query I need. I want to find all items that have country (P17)->Cyprus (Q229) and a value with instance of (P31) and that value (that item) must have instance of (P31)->dam (Q12323).

For example, Germasogeia Dam (Q22985821) is not in the list you create.

Both Germasogeia Dam (Q22985821) and Dipotamos Dam (Q62619235) must be in the list for example. Xaris333 (talk) 06:16, 17 January 2020 (UTC)

@Xaris333: I see. You need to be careful what you ask for: if you ask for P31=Q12323, that is what you'll be given. If, instead, you want items that are a subclass of Q12323 - let us say, that have a P31 which is a wdt:P279* of Q12323 - then that is what you'll be given:
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P17 wd:Q229 . 
  ?item wdt:P31/wdt:P279* wd:Q12323 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 12:28, 17 January 2020 (UTC)


@Xaris333, Tagishsimon: Just a comment. The modified query will give you the result you asked for, but not because earth-fill dam (Q47499777) is instance of (P31) dam (Q12323). You get it because earth-fill dam (Q47499777) is subclass of (P279) embankment dam (Q1244922) which is subclass of (P279) dam (Q12323). Same goes for rock-fill dam (Q11350418).
I think it is "structurally wrong" to set earth-fill dam (Q47499777) and rock-fill dam (Q11350418) as instance of (P31) dam (Q12323) as they both represent types/classes of dams and not instances/individuals of dams.
Also dome dam (Q56631985) and arch-gravity dam (Q357679), representing other types of dams, have incorrectly been given the property instance of (P31).
--Larske (talk) 15:04, 17 January 2020 (UTC)
I agree. How can we add the information what type of dam is according to the structure or material? Please read w:en:Dam#Types of dams. Xaris333 (talk) 10:07, 18 January 2020 (UTC)
If you want to introduce a "dam by material" class object which is subclass of (P279) dam (Q12323), you can do it similar to bridge by material (Q17645478), where you have both wooden bridge (Q428759) and metal bridge (Q3397572) being subclass of (P279) bridge by material (Q17645478) and steel bridge (Q12042110) is a subclass of (P279) metal bridge (Q3397572).
Another way, if you don't want to introduce more subclasses is to use the property made from material (P186) for the individual dam objects. Both ways are possible but the SPARQL question you need to select e.g. all "steel dams" are different:
  • In the first case you ask for ?dam wdt:P31 wd:"steel dam" . (where "steel dam" is the new class object)
    You also have to change the P31 property for all individual "steel dams" from the more generic dam (Q12323) to the more specific new class object "steel dam".
  • In the other case you ask for ?dam wdt:P31/P279* wd:Q12323; wdt:P186 wd:Q11427 .
    You also have to make sure that all individual "steel dams" have the made from material (P186) property set to steel (Q11427).
If there are several individual "steel dams", I think the first of these two alternatives is the better one.
--Larske (talk) 10:49, 18 January 2020 (UTC)
Don't make a new item for steel dam as there already is steel dam (Q16904906). --Dipsacus fullonum (talk) 13:26, 20 January 2020 (UTC)

Feature arcticle

Hello. I want to find all articles that have country (P17) -> Greece (Q41) and are featured article in English Wikipedia. Xaris333 (talk) 10:10, 18 January 2020 (UTC)

@Xaris333:
SELECT ?item ?itemLabel {
  ?item wdt:P17 wd:Q41 .
  ?enwp schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> .
  ?enwp wikibase:badge ?badge .
  FILTER(?badge=wd:Q17437796)  # featured article
#  FILTER(?badge=wd:Q17437798)  # good article
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 11:09, 18 January 2020 (UTC)

Thanks. Xaris333 (talk) 11:06, 19 January 2020 (UTC)

Famous (not french) people born in France

Is there any query that could help me get the famous people (not french) born in France ? Thanks !  – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).


Here is a basic one: 

SELECT ?item ?itemLabel ?nat ?natLabel ?pob ?pobLabel ?coor
{
    hint:Query hint:optimizer "None".
    ?item wdt:P19 / wdt:P17 wd:Q142 .
    MINUS { ?item wdt:P27 wd:Q142 } .
    ?item wdt:P27 ?nat .
    ?item wdt:P19 ?pob . 
    ?pob wdt:P17 wd:Q142 . OPTIONAL { ?pob wdt:P625 ?coor }
    ?item wdt:P31 wd:Q5 .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}

Try it!

It might need some work for Alsace. --- Jura 12:09, 20 January 2020 (UTC)

People by decade and century

Hello, how can I do a SPARQL search to count people born by decade? I would like to using date of birth for a group of people with a certain ethnic group, and would expect a result such as this: 1970 53 1980 44 1990 54 etc?

Regards, Robert

Something like this? --Dipsacus fullonum (talk) 19:34, 24 January 2020 (UTC)
# Count Danish citizens by birth decade
SELECT ?birth_decade (COUNT(?person) as ?persons)
WHERE
{
  ?person wdt:P31 wd:Q5.
  ?person wdt:P27 wd:Q35.
  ?person p:P569/psv:P569 [
    wikibase:timeValue ?dateOfBirth;
    wikibase:timePrecision ?precision
  ].
  FILTER(?precision >= "8"^^xsd:integer) # Precision is decade or smaller
  BIND(SUBSTR(STR(?dateOfBirth),1,3) as ?birth_decade)
}
GROUP BY ?birth_decade
ORDER BY ASC(?birth_decade)
Try it!
Thank you! Robertsilen (talk) 19:44, 25 January 2020 (UTC)
Is it possible to do counts by gender in separate columns? So male and female in own columns, and by decades in rows?

Articles on the Hebrew Wikipedia that have coordinates

Is it possible to make a query that shows a big list of all the articles on the Hebrew Wikipedia that have coordinates? WikiJunkie (talk) 00:32, 26 January 2020 (UTC)

@WikiJunkie: Something like this. The report server seems to be a bit all over the place right now - I've had this query work in between 21 seconds and 149 seconds, and I've had it timeout - hence the query is a bit more complex than it perhaps needs to be so as to optimise it as much as I'm able.
SELECT ?item ?itemLabel ?sitelink ?article ?coord with 
{ SELECT ?item ?coord ?article WHERE 
  {
    ?article schema:about ?item ;
      schema:isPartOf <https://he.wikipedia.org/> .
    ?item wdt:P625 ?coord .
} } as %i
where
{
  include %i
  ?sitelink ^schema:name ?article .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],he,en". }
}
Try it!
--Tagishsimon (talk) 01:17, 26 January 2020 (UTC)

List of films

Hello,

I need a list of 2019 films, where played Leo Dicaprio, Stallone and Tom Cruz (popular actors). Actually I need a list of 2019 films, where played popular actors, but its hard to automatically find out popular actors . --Alex Blokha (talk) 00:54, 29 January 2020 (UTC)

Below is some code. You can add Q-values (wd:Qxxxxxx) in the VALUES part for any actors you will include in the list. --Dipsacus fullonum (talk) 22:09, 29 January 2020 (UTC)
SELECT DISTINCT ?film ?filmLabel ?actorLabel
WHERE {
  ?film wdt:P31/wdt:P279* ?Q11424.
  ?film wdt:P577 ?date.
  filter(substr(str(?date),1,4) = "2019")
  ?film wdt:P161 ?actor.
  VALUES ?actor {
    wd:Q38111 # Leo Dicaprio
    wd:Q40026 # Sylvester Stallone
  }.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Countings airports

Hello, I'd like a count per all current countries, of how many civilian airports they currently have on their soil + their most up to date population of the country. Idem but with a date set : eg in year 2000, these numbers were x airports for y population.

Thanks ! Bouzinac (talk) 21:38, 29 January 2020 (UTC)

Here is for the first part – current airports and population. It seems both end time (P582) and dissolved, abolished or demolished date (P576) are in use for airports, so it tests that neither are defined. It relays on the best current population number having preferred rang. I wouldn't count too much on the accuracy of either airport count or population. --Dipsacus fullonum (talk) 01:44, 30 January 2020 (UTC)
SELECT ?country ?countryLabel ?no_of_airports ?population
WHERE
{
  {
    SELECT ?country (COUNT(?airport) AS ?no_of_airports)
    WHERE
    {
      ?airport wdt:P17 ?country.
      ?airport wdt:P31/wdt:P279* wd:Q1248784.
      OPTIONAL { ?airport (wdt:P582|wdt:P576) ?endtime. }
      FILTER(!BOUND(?endtime))
    }
    GROUP BY ?country
  }
  ?country wdt:P1082 ?population.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Below is a version year 2000. It will not show a population if none exist for the year, and it will have more than one row for the year if there is more than one population number for the year. Note that the year is used in strings 3 times in the query: 1 time as "2000", 1 time as "2000-01-01" and 1 time as "2001-01-01". I made the filter so the airport is required to have been open all of year, so if the airport is opened or closed in the year, it is not counted. I am not sure that I have (all) the right properties for opening/closing. --Dipsacus fullonum (talk) 03:14, 30 January 2020 (UTC)
SELECT ?country ?countryLabel ?no_of_airports ?population
WHERE
{
  {
    SELECT ?country (COUNT(?airport) AS ?no_of_airports)
    WHERE
    {
      ?airport wdt:P17 ?country.
      ?airport wdt:P31/wdt:P279* wd:Q1248784.
      OPTIONAL { ?airport wdt:P580|wdt:P571 ?starttime. }
      OPTIONAL { ?airport wdt:P582|wdt:P576 ?endtime. }
      FILTER((! BOUND(?endtime) || ?endtime >= "2001-01-01"^^xsd:dateTime) &&
             (! BOUND(?starttime) || ?starttime < "2000-01-01"^^xsd:dateTime))
    }
    GROUP BY ?country
  }
  OPTIONAL
  {
    ?country p:P1082 [
              ps:P1082 ?population;
              pq:P585 ?population_time
            ].
    FILTER(SUBSTR(STR(?population_time),1,4) = "2000")
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

how many trans-people in Wikidata?

Hey, I already know, that there are currently 6.323.322 humans registered in Wikidata. Now, I would like to know how many are women, man, transgender-male, transgender-female and intersex. However, my queries dont work out: 


SELECT (COUNT(?item) AS ?count)
WHERE
{
       ?human wdt:P31 wd:Q5 .
       ?transgender_male wdt:P21 wd:Q2449503  .
}
Try it! 


SELECT (COUNT(?item) AS ?count)
WHERE
{
       ?human wdt:P31 wd:Q5 .
       ?transgender_female wdt:P21 wd:Q1052281.
}
Try it! 


SELECT (COUNT(?item) AS ?count)
WHERE
{
       ?human wdt:P31 wd:Q5 .
       ?women wdt:P21 wd:Q467.
}
Try it!

and so on... Thank you for a tip! Eva EvaSeidlmayer (talk) 

SELECT ?sex ?sexLabel ?count
WITH
{
    SELECT ?sex (COUNT(?human) AS ?count)
    WHERE
    {
        ?human wdt:P31 wd:Q5.
        ?human wdt:P21 ?sex.
       VALUES ?sex {wd:Q48270 wd:Q1097630 wd:Q1052281 wd:Q2449503}.
    }
    GROUP BY ?sex
} AS %results
WHERE
{
    INCLUDE %results.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Try this. --Dipsacus fullonum (talk) 15:38, 25 January 2020 (UTC)

Count people by birth date decade and by gender?

I got this excellent SPARQL code earlier here. I'm wondering if I can add gender as columns? So one column for the count of males, and another for females by decade? (and I guess it might be easy to add other genders too).

I'd like to check how the distributions of gender on wikipedia has evolved through history. I did it with excel, but it would be nice to easily come back to this now and then to see how it looks.

Thank you very much for any tip! 


# Count Danish citizens by birth decade
SELECT ?birth_decade (COUNT(?person) as ?persons)
WHERE
{
  ?person wdt:P31 wd:Q5.
  ?person wdt:P27 wd:Q35.
  ?person p:P569/psv:P569 [
    wikibase:timeValue ?dateOfBirth;
    wikibase:timePrecision ?precision
  ].
  FILTER(?precision >= "8"^^xsd:integer) # Precision is decade or smaller
  BIND(SUBSTR(STR(?dateOfBirth),1,3) as ?birth_decade)
}
GROUP BY ?birth_decade
ORDER BY ASC(?birth_decade)
Try it!
@Robertsilen: Do you want gender only for persons with an article on a Wikipedia (in which language?), or for all persons with items on Wikidata? --Dipsacus fullonum (talk) 17:27, 30 January 2020 (UTC)
@Dipsacus: I'm interested in people with ethnic group "Swedish speaking population of Finland" and their Wikipedia page, or lack of such, in Swedish, Finnish, English, French and German. I'm trying to split the search result of 1430 people into gender & birth date by decade (as mentioned). I'm also trying to figure out what kind of search/grouping I could do with Occupation (P106) - maybe I could find contributers interested in writing about people in certain professions. Happy for all help with this :)
@Robertsilen: Below is code to find all citizens of Finland which have Swedish as native language grouped after decade of birth. It will show the number of articles in Swedish Wikipeia, and how many of these is male and female. If the sum of male and female is less than the total count, then some of the persons are something else (transgender) or the gender isn't specified. You can change the Wikipedia language code to show number for other Wikipedias, and you outcomment the Wikipedia line to show count for all persons on Wikidata. --Dipsacus fullonum (talk) 20:24, 30 January 2020 (UTC)
SELECT ?birth_decade (COUNT(?person) as ?persons) (SUM(?male) as ?male_count) (SUM(?female) as ?female_count)
WHERE
{
  ?link schema:about ?person; schema:isPartOf <https://sv.wikipedia.org/>.
  ?person wdt:P31 wd:Q5. # is a human
  ?person wdt:P27 wd:Q33. # citizen of Finland
  ?person wdt:P103 wd:Q9027. # native language Swedish
  ?person p:P569/psv:P569 [
      wikibase:timeValue ?dateOfBirth;
      wikibase:timePrecision ?precision
  ].
  FILTER(?precision >= "8"^^xsd:integer) # Precision is decade or smaller
  BIND(SUBSTR(STR(?dateOfBirth),1,3) as ?birth_decade)
  OPTIONAL { ?person wdt:P21 ?sex. }
  BIND(IF(?sex = wd:Q6581097,1,0) as ?male).
  BIND(IF(?sex = wd:Q6581072,1,0) as ?female).
}
GROUP BY ?birth_decade
ORDER BY ASC(?birth_decade)
Try it!

Get all values in two column format

Given these properties:

said to be the same as P460 Item this item is said to be the same as that item, but the statement is disputed Maidilibala <said to be the same as> Uskhal Khan Tögüs Temür - opposite of P461 Item item that is the opposite of this item black <opposite of> white opposite of fictional analog of P1074 Item used to link an entity or class of entities appearing in a creative work with the analogous entity or class of entities in the real world fictional spacecraft <fictional analog of> spacecraft - partially coincident with P1382 Item object that is partially part of, but not fully part of (P361), the subject Interstate 70 in Pennsylvania <partially coincident with> Interstate 76 - different from P1889 Item item that is different from another item, with which it is often confused Kowalski <different from> Kowalski - exact match P2888 URL (URLs only) used to link two items, indicating a high degree of confidence that the concepts can be used interchangeably soil <exact match> http://aims.fao.org/aos/agrovoc/c_7156 - territory overlaps P3179 Item part or all of the area associated with (this) entity overlaps part or all of the area associated with that entity Diocese of Auxerre <territory overlaps> Yonne - coextensive with P3403 Item this item has the same boundary as the target item; area associated with (this) entity is identical with the area associated with that entity Kings County <coextensive with> Brooklyn -

Say there are two entities related by property P3403 (or any of the bolded properties above). I want them listed in a two column format. That is,

if (entity_1, P3403, entity_2)

is a relation triplet

Then the result would contain 

Column 1                 Column 2

entity_1                 entity_2
..
...
....
entity_n                 entity_n2
Try it!

How do I get values related by these properties in a two column format (where-ever possible), that is for the entity pairs for the above bolded properties (a,b),(c,d),(e,f) and so on I want to see output of this form: 


Column1            Column2
a                  b

c                  d

e                  f
Try it!


 – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs). 


SELECT ?a ?aLabel ?aDescription ?b ?bLabel ?bDescription
{
    ?a wdt:P1889 ?b . 
    FILTER ( str(?a) < str(?b) ) 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 10

Try it!

I didn't read it in detail, but maybe the above can help you. --- Jura 22:46, 30 January 2020 (UTC)

Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2020/01&oldid=1114996905"