Wikidata:Request a query/Archive/2017/12

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

all items with name in some language

Hello! For example how can I get all items with official name (P1448) in Ukrainian language? I'm trying something like

SELECT ?item WHERE {
  ?item wdt:P1448 ?text .
  ?text wikibase:language "uk"
}

Try it!

But this does not work :( --Шмурак (talk) 10:32, 30 November 2017 (UTC)

SELECT ?item WHERE {
  ?item wdt:P1448 ?text .
  FILTER(LANG(?text) = 'uk')
}

Try it!

--Pasleim (talk) 10:41, 30 November 2017 (UTC)

• Pasleim, thank you a lot! One more question: if I want all items with specific text, I try

SELECT ?item WHERE {
  ?item wdt:P1448 ?text .
  FILTER(LANG(?text) = 'uk' . ?text = 'Коломия')
}

Try it!

but it also dos not work (I should get Kolomyia (Q52193) at least). --Шмурак (talk) 11:10, 30 November 2017 (UTC)

SELECT ?item WHERE {
  ?item wdt:P1448 ?text .
  FILTER(LANG(?text) = 'uk')
  FILTER(STR(?text) = 'Коломия')
}

Try it!

--Pasleim (talk) 11:21, 30 November 2017 (UTC)

Also FILTER( LANG( ?text ) = 'uk' && STR( ?text ) = 'Коломия' ) or FILTER( ?text = 'Коломия'@uk ). Matěj Suchánek (talk) 16:22, 30 November 2017 (UTC)

@Шмурак, Pasleim, Matěj Suchánek: if the text and language are both constant, then searching for the text literal directly is a lot more efficient:

SELECT ?item WHERE {
  ?item wdt:P1448 "Коломия"@uk .
}

Try it!

When I tried it just now, the time was some 300 ms instead of about 13 s :) --TweetsFactsAndQueries (talk) 08:49, 2 December 2017 (UTC)

Data model for bus stops

I'm looking into how bus stops have been modeled here so far, and this is what I got:

The following query uses these:

• Items: bus stop (Q953806)   

• Properties: instance of (P31)   

  SELECT DISTINCT ?property ?propertyLabel WHERE {
  #  ?property rdf:type wikibase:Property .
    ?bus_stop wdt:P31 wd:Q953806 ;
             ?property [ ] .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

The results make sense to me overall, but the formatting is irritating — I would have expected it to be more like in the Listeria query above:

The following query uses these:

• Properties: subproperty of (P1647)   

  SELECT ?property2 ?property2Label ?property1 ?property1Label WHERE {
    ?property1 rdf:type wikibase:Property. #not replaceable with wikibase:Item, wikibase:Statement, wikibase:Reference at WDQS
    ?property1 wdt:P1647 ?property2. 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
  

Try it!

Any pointers? --Daniel Mietchen (talk) 23:29, 30 November 2017 (UTC)

This is due to the difference between the property entity itself (/entity/P31) and its predicates (/prop/P31 aka p:P31, /prop/direct/P31 aka wdt:P31, etc.). You want to select the property belonging to a certain “claim” predicate.

SELECT DISTINCT ?property ?propertyLabel WHERE {
  ?bus_stop wdt:P31 wd:Q953806 ;
           ?p [ ] .
  ?property a wikibase:Property;
            wikibase:claim ?p.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

A full list of predicates linking the property to its predicates (like wikibase:claim here) is on mw:Wikibase/Indexing/RDF Dump Format#Properties. --TweetsFactsAndQueries (talk) 13:18, 1 December 2017 (UTC)

And here’s another slightly improved version: with grouping, so that you can see which properties are commonly used and which are just present on a handful of bus stops, and also including subclasses of bus stop (Q953806) (which apparently exist).

SELECT ?property ?propertyLabel (COUNT(*) AS ?count) WHERE {
  ?bus_stop wdt:P31/wdt:P279* wd:Q953806 ;
           ?p [ ] .
  ?property a wikibase:Property;
            wikibase:claim ?p.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?property ?propertyLabel
ORDER BY DESC(?count)

Try it!

--TweetsFactsAndQueries (talk) 13:23, 1 December 2017 (UTC)

Looks great — thanks! --Daniel Mietchen (talk) 02:36, 2 December 2017 (UTC)

Extracting first letter of a name (and group it by frequency)

Hello! I'm trying to extract the first letter of a string, like for example, in this list of sovereign states presidents:

The following query uses these:

• Items: sovereign state (Q3624078)   

• Properties: instance of (P31)   , head of government (P6)   

  SELECT ?Estatu_subiranoaLabel ?agintariaLabel WHERE {
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    ?Estatu_subiranoa wdt:P31 wd:Q3624078.
    ?Estatu_subiranoa wdt:P6 ?agintaria
  }
  

Try it!

But I can't find a way to GREP the result in the documentation. Could someone help? -Theklan (talk) 10:58, 2 December 2017 (UTC)

The following query uses these:

• Items: sovereign state (Q3624078)   

• Properties: instance of (P31)   , head of government (P6)   

  SELECT ?first_letter (count(?first_letter) as ?count) WHERE {
    ?Estatu_subiranoa wdt:P31 wd:Q3624078;
                   rdfs:label ?Estatu_subiranoaLabel;
                   wdt:P6 ?agintaria.
    ?agintaria rdfs:label ?agintariaLabel.
    FILTER(lang(?Estatu_subiranoaLabel)="en" && lang(?agintariaLabel)="en") 
    bind( substr( ?agintariaLabel, 1, 1 ) as ?first_letter )
  }
  GROUP BY ?first_letter
  ORDER by DESC(?count)
  

Try it!

If this is what you are looking for. John Samuel 11:15, 2 December 2017 (UTC)

Exactly that, thanks @Jsamwrites:! -Theklan (talk) 21:42, 5 December 2017 (UTC)

French communes and their liberation date

Hello everyone, is there a history property so that one can query all 36000 french communes and know their liberation from Nazis date (be it empty or input) ? Or any query showing liberation date + town name 1944-1945 ? Thanks ! --Bouzinac (talk) 16:03, 4 December 2017 (UTC)

I checked a few of them and it seems we don't have this data yet. The property for this could be significant event (P793): liberation from occupation with point in time (P585). If you have a dataset for this, it should be easy to import and then query for it. Matěj Suchánek (talk) 17:49, 4 December 2017 (UTC)

Would this list Chronologie de la Libération en France be of any help ? The most noticeable liberations would be Ajaccio 09 /09/1943 ; Bayeux 07 /06/1944 ; Paris 25 / 08 /1944 ; Saint Nazaire 11 / 05 / 1945 among many others--Bouzinac (talk) 20:28, 4 December 2017 (UTC)

More than one value

Hello. I want to find all items that have instance of (P31) with national association football team (Q6979593) and have more than 1 value with competition class (P2094). Xaris333 (talk) 22:09, 4 December 2017 (UTC)

SELECT ?item ?cc1 ?cc2 WHERE {
  ?item wdt:P31 wd:Q6979593 .
  ?item wdt:P2094 ?cc1 .
  ?item wdt:P2094 ?cc2 .
  FILTER(?cc1 != ?cc2) .
}

Try it!

There are none. —MisterSynergy (talk) 22:18, 4 December 2017 (UTC)

Thanks! Xaris333 (talk) 19:23, 5 December 2017 (UTC)

List of all properties that are instance of (P31) property likely to be challenged (Q44597997) along with the usage count of every one of the properties

For the project of a Living People policy, I want to have a Listeria list of properties that are instance of (P31) property likely to be challenged (Q44597997). Can someone write me an appropriate Query that I can copy into Listeria? ChristianKl (✉) 20:23, 5 December 2017 (UTC)

{{Wikidata list|sparql=SELECT ?item ?property ?wikilink (COUNT(?p) AS ?cnt) WHERE {
  wd:Q44597997 ^wdt:P31 [ wdt:P1629 ?item ] .
  ?item wdt:P1687 ?property .
  BIND(CONCAT('[[Property:', SUBSTR(STR(?property), 32), ']]') AS ?wikilink) .
  ?property wikibase:claim ?p .
  [] ?p [] .
} GROUP BY ?item ?property ?wikilink
|columns=?item:Item,?wikilink:Property,?cnt:Number of claims|links=all|autolist=fallback}}
{{Wikidata list end}}

Not a beauty, but Listeria’s inability to deal with entities other than items makes such hacks necessary. —MisterSynergy (talk) 21:16, 5 December 2017 (UTC)

Thanks. ChristianKl (✉) 23:37, 5 December 2017 (UTC)

Find all profiles with P3217 and more values on P569 and also P570

E.g. Lars Hjortsberg (Q5807838) has two date of birth (P569) values

• 22 november 1772 - imported from the Wikipedia article
• 23 november 1772 - created from the mixandmatch import 528

I would like to have a query to see all P3217 related profiles with more values on date of birth (P569) and also for date of death (P570) and display

• value
• if date the calendar used
• ranking
• ref

as the Wikipedia import is the same as the article and the P3217 is from a professional researched source by Swedish National Archives we get profiles that need to be checked in sv:Wikipedia

Thanks I get lost on the p and ps and..... - Salgo60 (talk) 15:27, 1 December 2017 (UTC)

The following query uses these:

• Items: proleptic Gregorian calendar (Q1985727)   , proleptic Julian calendar (Q1985786)   

• Properties: Dictionary of Swedish National Biography ID (P3217)   , Wikimedia language code (P424)   , date of birth (P569)   , reference URL (P854)   , stated in (P248)   , imported from Wikimedia project (P143)   

  SELECT ?item ?date1 ?p1 ?c1 ?r1 ?refurl1 ?angesi1Label ?i1 ?date2 ?p2 ?c2 ?r2 ?refurl2 ?angesi2Label ?i2 WHERE
  {
    ?item wdt:P3217 ?sbl .
    ?item p:P569 ?date1stm .
    ?date1stm psv:P569 ?date1node .
    ?date1node wikibase:timeValue ?date1 .
    ?date1node wikibase:timePrecision ?p1 .
    ?date1node wikibase:timeCalendarModel ?cal1 .
    BIND(IF(?cal1=wd:Q1985727,'G',IF(?cal1= wd:Q1985786, 'J', '?')) AS ?c1) 
    ?date1stm wikibase:rank ?rank1 .
    BIND(IF(?rank1=wikibase:PreferredRank,'P',IF(?rank1= wikibase:NormalRank, 'N', 'D')) AS ?r1) 
    OPTIONAL { ?date1stm prov:wasDerivedFrom ?ref1.
       OPTIONAL { ?ref1 pr:P854 ?refurl1 . }
       OPTIONAL { ?ref1 pr:P248 ?angesi1 . }
       OPTIONAL { ?ref1 pr:P143/wdt:P424 ?i1 . }
    }
    ?item p:P569 ?date2stm .
    ?date2stm psv:P569 ?date2node .
    ?date2node wikibase:timeValue ?date2 . 
    ?date2node wikibase:timePrecision ?p2 .
    ?date2node wikibase:timeCalendarModel ?cal2 .
    BIND(IF(?cal2=wd:Q1985727,'G',IF(?cal2= wd:Q1985786, 'J', '?')) AS ?c2) 
    ?date2stm wikibase:rank ?rank2 .
    BIND(IF(?rank2=wikibase:PreferredRank,'P',IF(?rank2= wikibase:NormalRank, 'N', 'D')) AS ?r2)
    OPTIONAL { ?date2stm prov:wasDerivedFrom ?ref2 .
       OPTIONAL { ?ref2 pr:P854 ?refurl2 . }
       OPTIONAL { ?ref2 pr:P248 ?angesi2 . }
       OPTIONAL { ?ref2 pr:P143/wdt:P424 ?i2 . }
    }
    FILTER(?date1<?date2)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
  }
  ORDER BY ?item ?refurl1 ?refurl2
  

Try it!

I added columns for timePrecision (?p) and importedFrom (?i). Change P569 to P570 (in 4 places) to get death dates instead of birth dates.

--Larske (talk) 22:46, 7 December 2017 (UTC)

Find Items with number of statements!

At WIKIDATACON, it was mentioned that eventually items could be found, based on quality! But, until then;

Is there a way to find/list/query items with the number of statements it contains?

and, items with no labels in a particular language? or items with only one label, with no additional languages?

and, items that have no "instance of and subclass of" statements? (without using the game!)

and, items with no external identifiers?

If it has already been answered!? I apologise for asking again!  – The preceding unsigned comment was added by Wallacegromit1 (talk • contribs) at 10:15, 7 December 2017‎ (UTC).

Yes all of that is technically possible with the Query Service—but it only works if you restrict it to certain topics (otherwise the queries would time out). So could you please ask a more specific question? —MisterSynergy (talk) 09:22, 7 December 2017 (UTC)

Thanks for the swift reply!

More specific questions would be;

Query:

1. items with only 1(one) or 2 statements (example: only Commons Category, Or, Commons Category and Topics Main Category)?

2. items with labels only in one language(en)?

3. items with only Freebase external identifiers?

items with exactly two statements

SELECT DISTINCT ?item ?itemLabel
WHERE{
  ?item wikibase:statements 2 .
  FILTER ( ?item not in ( wd:Q16943273,wd:Q17566023 ) ) #Exclude item of wikidata tour
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
} 
Limit 100

Try it!

 – The preceding unsigned comment was added by Bigbossfarin (talk • contribs) at 12:41, 7 December 2017‎ (UTC).

Items with no other statements except the Freebase ID (P646):

SELECT ?item WHERE {
  ?item wdt:P646 ?freebaseId;
        wikibase:statements "1"^^xsd:integer.
}

Try it!

--TweetsFactsAndQueries (talk) 12:46, 7 December 2017 (UTC)

I think (2.) is not possible, this one is similar:

items with English but no French label

SELECT ?item ?label_en WHERE {
  ?item rdfs:label ?label_en filter(lang(?label_en) = "en") .
  MINUS { ?item rdfs:label ?label_fr filter(lang(?label_fr) = "fr") . }
}
LIMIT 100

Try it!

Bigbossfarin (talk) 13:02, 7 December 2017 (UTC)

items without instance of and subclass of

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

Try it!

Bigbossfarin (talk) 13:12, 7 December 2017 (UTC)

Thanks a lot, learned a lot of new syntax!

With out a qualifier

Hello. I need:

• a query that finds items with ranking (P1352) with any value and with review score by (P447) --> FIFA (Q253414) as a qualifier, but with not determination method (P459) as a qualifier.

• a query that finds items with ranking (P1352) with any value and with review score by (P447) --> FIFA (Q253414) as a qualifier, but with not points for (P1358) as a qualifier. if and only if the qualifier point in time (P585) has a date later than 2011-08-23.

Xaris333 (talk) 09:51, 8 December 2017 (UTC)

1. SELECT ?item ?value WHERE {
     ?item p:P1352 ?s .
     ?s ps:P1352 ?value; pq:P447 wd:Q253414 .
     MINUS { ?s pq:P459 [] }
   }
   

   Try it!

2. SELECT ?item ?value ?time WHERE {
     ?item p:P1352 ?s .
     ?s ps:P1352 ?value; pq:P447 wd:Q253414; pqv:P585 [ wikibase:timeValue ?time ] .
     MINUS { ?s pq:P1358 [] }
     FILTER(?time > "+2011-08-23T00:00:00Z"^^xsd:dateTime) .  
   }
   

   Try it!

—MisterSynergy (talk) 11:08, 8 December 2017 (UTC)

Thanks! Xaris333 (talk) 14:31, 8 December 2017 (UTC)

Article title and label

Hello. I need a query that finds items with instance of (P31) --> national association football team (Q6979593) and the English Wikipedia article has different title from the English label. Is that possible? Xaris333 (talk) 00:19, 9 December 2017 (UTC)

SELECT ?item ?label ?name ?sitelink WHERE {
  ?item wdt:P31 wd:Q6979593 .
  ?item rdfs:label ?label .
  FILTER(LANG(?label) = 'en') .
  ?sitelink schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?name .
  FILTER(?label != ?name) .
}

Try it!

—MisterSynergy (talk) 06:19, 9 December 2017 (UTC)

Thanks. Xaris333 (talk) 02:49, 10 December 2017 (UTC)

Items that got this reward: Kasper Salin Prize (Q3432243)

I would like to create a query that lists all items that has gotten the Kasper Salin Prize (Q3432243) award, preferably sorted by date, since one winner will be awarded each year since 1960's. How to do this?  – The preceding unsigned comment was added by Mippzon (talk • contribs) at 19:04, 10 December 2017‎ (UTC).

SELECT ?item ?itemLabel ?year WHERE {
  ?item p:P166 ?s .
  ?s ps:P166 wd:Q3432243 .
  OPTIONAL { ?s pq:P585 ?time . BIND(YEAR(?time) AS ?year) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?year)

Try it!

Only six results. Guess you need to add the data first (use award received (P166) with qualifiers point in time (P585)). —MisterSynergy (talk) 18:11, 10 December 2017 (UTC)

Wow, thanks for swift reply (and for adding my signature, since I forgot that). Exactly what I needed! //Mippzon (talk) 18:15, 10 December 2017 (UTC)

Would be nice to show the image of the objects as well, but I can't get this to work with the query helper. The column appears, but no image, even if image exists in item? //Mippzon (talk) 18:23, 10 December 2017 (UTC)

Not sure whether the query service can display images at all. It would probably be better to make a Listeria list for that, once the data set is complete. —MisterSynergy (talk) 18:33, 10 December 2017 (UTC)

It is possible:

#defaultView:ImageGrid
SELECT ?item ?itemLabel ?year ?img WHERE {
  ?item p:P166 ?s .
  ?s ps:P166 wd:Q3432243 .
  OPTIONAL { ?s pq:P585 ?time . BIND(YEAR(?time) AS ?year) } .
  OPTIONAL { ?item wdt:P18 ?img } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?year)

Try it!

See Wikidata:SPARQL query service/Wikidata Query Help/Result Views. Matěj Suchánek (talk) 18:55, 10 December 2017 (UTC)

Thanks! I was actually going for Listeria. I've done one attempt here but I can't get the years right. I want my Listeria to look something like this. Any tips? //Mippzon (talk) 21:34, 10 December 2017 (UTC)

Duplicate rows in result set?

In this simple hack of a Wikidata weekly summary report, please explain why I'm getting what look like duplicate rows in the result set - e.g. for Gandhi (Q202211). I know I could use SELECT DISTINCT; but I don't know why some items result in dupes whilst others do not. I'm not seeing the proliferation of values in ?film ?filmLabel ?statusLabel that woud lead to the pattern of dupes I see.

Also, why does adding GROUP BY ?film to the foot of the query break it?

SELECT ?film ?filmLabel ?statusLabel WHERE {
  ?film wdt:P31 wd:Q11424 ;
            wdt:P577 ?date .
            OPTIONAL {?film wdt:P1552 ?status .} 
  FILTER("1983-01-01"^^xsd:dateTime <= ?date && ?date < "1984-01-01"^^xsd:dateTime).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

thx --Tagishsimon (talk)

The reason for having two result rows for Gandhi (Q202211) is that this film has three values for publication date (P577) of which two dates pass you date filter.

SELECT ?film ?filmLabel ?date ?statusLabel WHERE {
  ?film wdt:P31 wd:Q11424 ;
            wdt:P577 ?date .
            OPTIONAL {?film wdt:P1552 ?status .} 
  FILTER(?film=wd:Q202211)
  FILTER("1983-01-01"^^xsd:dateTime <= ?date && ?date < "1984-01-01"^^xsd:dateTime).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

--Larske (talk) 01:56, 12 December 2017 (UTC)

Thanks. Completely overlooked P577. --Tagishsimon (talk) 02:00, 12 December 2017 (UTC)

Disasters starting on Friday 13

The main issue here is how to infer the day of the week. Here's what I got so far:

The following query uses these:

• Items: disaster (Q3839081)   

• Properties: instance of (P31)   , subclass of (P279)   , start time (P580)   

  SELECT ?disaster ?disasterLabel WHERE {
    ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
              wdt:P580 ?start_time .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
  

Try it!

Now how to go about finding those that happened on a Friday? --Daniel Mietchen (talk) 02:40, 15 December 2017 (UTC)

We do have items like October 13, 2017 (Q37788009). If all of them had point in time (P585) and somehow indicated the day of week, this would be piece of cake. Still, the day of week can be computed (I used w:cs:Algoritmus pro výpočet dne v týdnu#Zellerův algoritmus):

# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT ?disaster ?disasterLabel ?start_time WHERE {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            wdt:P580 ?start_time .
  BIND( DAY( ?start_time ) AS ?q ) .
  FILTER( ?q = 13 ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  FILTER( ?_h - FLOOR( ?_h / 7 ) * 7 = 6 ) . # modulo, 0 is Saturday
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } .
}

Try it!

Matěj Suchánek (talk) 09:05, 15 December 2017 (UTC)

Tuesday 26 is the date...

...with the fewest disaster starts. Just out of curiosity I modified the question above to present the frequency of disaster starts for each date/weekday combination. Based on the 1,556 disasters with a starting date given with a precision on date level, it seems that the most frequent date is the 25th with 68 disaster starts, 13th only have 59, and the most frequent weekday is Wednesday with 235 disaster starts, Friday only have 219. However, the most frequent date/weekday combination is not Wednesday 25, it is a tie between Tuesday 22 and Saturday 25, both with 15 disaster starts. Friday 13 does only count for 10 disaster starts. Tuesday 26 seems to be the "safest" with just one disaster start.

# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT (?q AS ?Date) (SUM(?mon) AS ?Mon) (SUM(?tue) AS ?Tue) (SUM(?wed) AS ?Wed) (SUM(?thu) AS ?Thu) (SUM(?fri) AS ?Fri) (SUM(?sat) AS ?Sat) (SUM(?sun) AS ?Sun)  (COUNT(?start_time) AS ?Tot)  WHERE {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P580 ?start_time_stm .
  ?start_time_stm ps:P580 ?start_time .
  ?start_time_stm psv:P580 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11)
  BIND( DAY( ?start_time ) AS ?q ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  BIND( ?_h - FLOOR( ?_h / 7 ) * 7 AS ?weekday) . # modulo, 0 is Saturday
  BIND(IF(?weekday=2,1,0) AS ?mon)
  BIND(IF(?weekday=3,1,0) AS ?tue)
  BIND(IF(?weekday=4,1,0) AS ?wed)
  BIND(IF(?weekday=5,1,0) AS ?thu)
  BIND(IF(?weekday=6,1,0) AS ?fri)
  BIND(IF(?weekday=0,1,0) AS ?sat)
  BIND(IF(?weekday=1,1,0) AS ?sun)
}
GROUP BY ?q
ORDER BY ?Date

Try it!

--Larske (talk) 14:37, 15 December 2017 (UTC)

@Larske, Matěj Suchánek, Daniel Mietchen: - these are awesome, but it occurred to me that a lot of events use point in time (P585) not start time (P580), especially for things like earthquakes or air crashes - there are 3600 with P585 and 1637 with P580. Putting these into the script gives us a tie between Mon 12th and Tue 22nd for most common (38 events). The least common is Sun 17th, Wed 17th, or Mon 30th, all with 12. Friday 13th has 24, very close to the average of 22.6. Andrew Gray (talk) 17:01, 15 December 2017 (UTC)

# using https://cs.wikipedia.org/wiki/Algoritmus_pro_v%C3%BDpo%C4%8Det_dne_v_t%C3%BDdnu#Zeller%C5%AFv_algoritmus
SELECT (?q AS ?Date) (SUM(?mon) AS ?Mon) (SUM(?tue) AS ?Tue) (SUM(?wed) AS ?Wed) (SUM(?thu) AS ?Thu) (SUM(?fri) AS ?Fri) (SUM(?sat) AS ?Sat) (SUM(?sun) AS ?Sun)  (COUNT(?start_time) AS ?Tot)  WHERE {
 { 
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P580 ?start_time_stm .
  ?start_time_stm ps:P580 ?start_time .
  ?start_time_stm psv:P580 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11) 
 }
 UNION # to cover both "point in time" and "start time" events
 {
  ?disaster wdt:P31/wdt:P279* wd:Q3839081 ;
            p:P585 ?start_time_stm .
  ?start_time_stm ps:P585 ?start_time .
  ?start_time_stm psv:P585 ?start_time_node .
  ?start_time_node wikibase:timePrecision ?tp . FILTER(?tp=11) 
 }
  BIND( DAY( ?start_time ) AS ?q ) .
  BIND( MONTH( ?start_time ) + IF( MONTH( ?start_time ) < 3, 12, 0 ) AS ?m ) .
  BIND( YEAR( ?start_time ) - IF( MONTH( ?start_time ) < 3, 1, 0 ) AS ?_year ) .
  BIND( ?_year - FLOOR( ?_year / 100 ) * 100 AS ?K ) . # modulo
  BIND( FLOOR( YEAR( ?start_time ) / 100 ) AS ?J ) .
  BIND( ?q + FLOOR( 13 * ( ?m + 1 ) / 5 ) + ?K + FLOOR( ?K / 4 ) + FLOOR( ?J / 4 ) - 2 * ?J AS ?_h ) .
  BIND( ?_h - FLOOR( ?_h / 7 ) * 7 AS ?weekday) . # modulo, 0 is Saturday
  BIND(IF(?weekday=2,1,0) AS ?mon)
  BIND(IF(?weekday=3,1,0) AS ?tue)
  BIND(IF(?weekday=4,1,0) AS ?wed)
  BIND(IF(?weekday=5,1,0) AS ?thu)
  BIND(IF(?weekday=6,1,0) AS ?fri)
  BIND(IF(?weekday=0,1,0) AS ?sat)
  BIND(IF(?weekday=1,1,0) AS ?sun)
}
GROUP BY ?q
ORDER BY ?Date

Try it!

Oh, so this Sunday will be a safe day :)
--- Jura 17:15, 15 December 2017 (UTC)

As someone with a birthday on the 17th of a month, I am certainly happy with these results :-) Andrew Gray (talk) 19:51, 15 December 2017 (UTC)

Looks like the minimum for the 17th is lower than for the 31st (overall lowest) :)
--- Jura 20:34, 15 December 2017 (UTC)

Comparing dates greater than 28th is not completely fair as they are not as frequent as dates 1st–28th. In a year are only 7 days with date 31, 11 with date 30, and 11 (or 12 for leap years) with date 29 compared to 12 days with date 1, 2, 3, ..., 28. So the date 31st is in fact the most "risky" when it comes to disasters. --Larske (talk) 09:14, 16 December 2017 (UTC)

Maybe it's the least risky as so many already happened that day ;)
BTW, I created Wikidata:WikiProject Calendar Dates.
--- Jura 09:22, 16 December 2017 (UTC)

@Larske, Matěj Suchánek, Andrew Gray, Jura1: I am enjoying both the queries themselves, which taught me some new bits of SPARQL, and the results, which provide for great starting points to poke around in history. Thanks! --Daniel Mietchen (talk) 01:58, 17 December 2017 (UTC)

Source

Hello. I need a query that finds items with ranking (P1352) with any value and with review score by (P447) --> FIFA (Q253414) as a qualifier, but the source has no reference URL (P854). Xaris333 (talk) 10:39, 16 December 2017 (UTC)

SELECT ?item ?itemLabel ?rankingValue ?pointInTime WHERE {
  ?item p:P1352 ?s .
  ?s ps:P1352 ?rankingValue; pq:P447 wd:Q253414 .
  OPTIONAL { ?s pq:P585 ?pointInTime }
  MINUS { ?s prov:wasDerivedFrom [ pr:P854 [] ] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],en' }
}

Try it!

—MisterSynergy (talk) 10:47, 16 December 2017 (UTC)

Thanks. Xaris333 (talk) 10:50, 16 December 2017 (UTC)

Future events

I'm looking at ways to build a timeline of future events. To this end, I've tried to adapt the Recent Events example query to the future, which works for small LIMITs but times out otherwise:

The following query uses these:

• Items: occurrence (Q1190554)   

• Properties: instance of (P31)   , subclass of (P279)   , point in time (P585)   , start time (P580)   

  #Future Events
  SELECT DISTINCT ?event ?eventLabel ?date
  WHERE
  {
  	# find events
  	?event wdt:P31/wdt:P279* wd:Q1190554.
  	# with a point in time or start date
  	OPTIONAL { ?event wdt:P585 ?date. }
  	OPTIONAL { ?event wdt:P580 ?date. }
  	# but at least one of those
  	FILTER(BOUND(?date) && DATATYPE(?date) = xsd:dateTime).
  	# in the future
  	BIND(NOW() - ?date AS ?distance).
  	FILTER(0 >= ?distance ).
  	# and get a label as well
  	OPTIONAL {
  		?event rdfs:label ?eventLabel.
  		FILTER(LANG(?eventLabel) = "en").
  	}
  }
  # limit to 100 results so we don't timeout
  LIMIT 100
  

Try it!

Given that Wikidata does (or at least should have) way less events in the future than in the past, can we take advantage of that to make the query more efficient? I've tried to do the check for data type in two different ways, but neither of them move things forward:

The following query uses these:

• Items: Wikidata property with datatype 'time' (Q18636219)   

• Properties: instance of (P31)   

  #Future Events
  SELECT ?event ?eventLabel ?date
  WHERE
  {
  	# find events
      ?event ?timeproperty ?date.
  	FILTER(BOUND(?date) && DATATYPE(?date) = xsd:dateTime).
      #?timeproperty wdt:P31 wd:Q18636219 .
  
      # in the future
  	BIND(NOW() - ?date AS ?distance).
  	FILTER(0 >= ?distance).
  	# and get a label as well
  	OPTIONAL {
  		?event rdfs:label ?eventLabel.
  		FILTER(LANG(?eventLabel) = "en").
  	}
  }
  ORDER BY ?date
  # limit to 10 results so we don't timeout
  LIMIT 10
  

Try it!

Any alternative suggestions? Thanks, --Daniel Mietchen (talk) 17:44, 18 December 2017 (UTC)

In the first example, the expensive operation is ?event wdt:P31/wdt:P279* wd:Q1190554, so I suggest to do it as late as possible and optimize at that step. This query executes in ~2 seconds and includes all ~1600 items with future points in time in P580 and P585, but the occurrence (Q1190554) criterion is missing.

The second one is practically impossible due to the large number of P577 claims (>12M, as opposed to 37k for P580 and 135k for P585; the latter two are typically used as qualifiers, not direct claims). —MisterSynergy (talk) 18:26, 18 December 2017 (UTC)

Thanks — that's good enough for me for the moment, as I'm using the query for quality checks. --Daniel Mietchen (talk) 00:57, 19 December 2017 (UTC)

Date output format

How do I get the format of my date to be YYYY-MM-DD in the output of the following? thx --Tagishsimon (talk) 15:08, 19 December 2017 (UTC)

SELECT ?item ?itemLabel ?wikidata_groupLabel ?genderLabel ?areaLabel ?DoB
WHERE {
  ?item p:P39 ?mem.
  ?mem ps:P39 wd:Q18607856.
  OPTIONAL { ?mem pq:P4100|pq:P102 ?wikidata_group. }
  OPTIONAL { ?mem pq:P768 ?area. }
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P569 ?DoB. }
  FILTER NOT EXISTS { ?mem pq:P582 []. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".}
}

Try it!

And more specifically, with leading zeros. This is as close as I've got yet:

SELECT ?item ?itemLabel ?wikidata_groupLabel ?genderLabel ?areaLabel (CONCAT(STR(YEAR(?DoB)), 
                     "-", 
                     STR(MONTH(?DoB)), 
                     "-", 
                    STR(DAY(?DoB))) as ?displayDate)
WHERE {
  ?item p:P39 ?mem.
  ?mem ps:P39 wd:Q18607856.
  OPTIONAL { ?mem pq:P4100|pq:P102 ?wikidata_group. }
  OPTIONAL { ?mem pq:P768 ?area. }
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P569 ?DoB. }
  FILTER NOT EXISTS { ?mem pq:P582 []. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".}
}

Try it!

As I vaguely understand it, dates are stored as 2011-01-10T14:45:13.815-05:00"^^xsd:dateTime, so I sense I'm missing a trick. --Tagishsimon (talk) 15:12, 19 December 2017 (UTC)

(str(?DoB) as ?string) gets you 1944-06-01T00:00:00Z .
--- Jura 15:20, 19 December 2017 (UTC)

Thanks Jura. BIND(substr(str(?DoB),1,10) as ?displayDate) :) --Tagishsimon (talk) 16:27, 19 December 2017 (UTC)

eBird vs. ABA

eBird taxon ID (P3444) are derived, in part, from ABA bird ID (P4526), but with some differences. Please can we have a query for items with both IDs, where the value differs? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 13:49, 20 December 2017 (UTC)

Maybe this is useful for further evaluation:

SELECT ?item ?ebird ?ababird ?match WITH {
  SELECT ?item WHERE {
    { ?item wdt:P3444 [] } UNION { ?item wdt:P4526 [] }
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  OPTIONAL { ?item wdt:P3444 ?ebird }
  OPTIONAL { ?item wdt:P4526 ?ababird }
  BIND(IF(?ebird = ?ababird, 'same', 'different') AS ?match) .
}

Try it!

—MisterSynergy (talk) 14:42, 20 December 2017 (UTC)

Or for the 430 records you're after (probably):

SELECT ?item ?itemLabel ?ebird ?ababird WHERE {
  ?item wdt:P3444 ?ebird.
  ?item wdt:P4526 ?ababird .
  BIND(IF(?ebird != ?ababird, 'different', 'same') AS ?match) .
  FILTER(?match!='same').
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
  }
}

Try it!

--Tagishsimon (talk) 15:38, 20 December 2017 (UTC)

@MisterSynergy, Tagishsimon: Thank you. 430 is more than I was expcting, but that's just the answer I wanted. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 10:31, 21 December 2017 (UTC)

List works of art and the artist in (Holland, Belgium, France) between two dates

The query gives errors and I don't know if I'm on the right track. Also a painting may be attributed only as the more generic 'work of art', so would it be necessary to stipulate both? Any suggestions would be appreciated.

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
#defaultView:table
SELECT ?item ?itemLabel ?title ?createDate ?artist ?givenName ?familyName ?birthPlace ?creatorLabel WHERE {
  ?creator (wdt:P31/wdt:P279*) wd:Q1028181.
  ?creator wdt:P27 ?nationality.
  ?item (wdt:P31/wdt:P279*) wd:Q838948.
  ?item wdt:P170 ?creator.
  ?item wdt:P971 wd:Q37617783.
  VALUES (?nationality) { Q31 Q55 Q142}
  }
  OPTIONAL {
    ?creator wdt:P735 ?givenName.
    ?creator wdt:P734 ?familyName.
    ?item wdt:P1476 ?title.
    ?item wdt:P571 ?inception.
    ?item wdt:P17 ?createCountry.
    ?item wdt:P608 wd:Q20980830.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl,fr,es". }
  ?artist wdt:P27 wd:Q26.
  FILTER(?inception >= "1521-01-01"^^xsd:date)
  FILTER(?inception <= "1680-01-01"^^xsd:date)
}
ORDER BY ASC(?inception)	
LIMIT 100

 – The preceding unsigned comment was added by Dmcguerty (talk • contribs) at 22. 12. 2017, 09:20‎ (UTC).

SELECT ?item ?itemLabel ?title ?inception ?givenName ?familyName ?creatorLabel WHERE {
  VALUES ?nationality { wd:Q31 wd:Q55 wd:Q142 } .
  ?creator wdt:P106 wd:Q1028181.
  ?creator wdt:P27 ?nationality.
  ?creator ^wdt:P170 ?item .
  ?item wdt:P571 ?inception .
  ?item wdt:P31/wdt:P279* wd:Q838948 .
  FILTER(?inception >= "1521-01-01"^^xsd:dateTime) .
  FILTER(?inception <= "1680-01-01"^^xsd:dateTime) .
  OPTIONAL { ?creator wdt:P735 ?givenName } .
  OPTIONAL { ?creator wdt:P734 ?familyName } .
  OPTIONAL { ?item wdt:P1476 ?title } .
  #OPTIONAL { ?item wdt:P17 ?createCountry } .
  #OPTIONAL { ?item wdt:P608 wd:Q20980830 } .
  #?item wdt:P971 wd:Q37617783.
  #?artist wdt:P27 wd:Q26.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl,fr,es" } .
}
ORDER BY ASC(?inception)	
LIMIT 100

Try it!

This query should work. I couldn't work out what some statements (lines) were supposed to do, so I commented them out (#).

Also, be aware of phab:T159160. Matěj Suchánek (talk) 14:08, 22 December 2017 (UTC)

Paintings with images  : Out of time query

Hi, I'm looking for all the paitings in Wikidata that have an image attached but my query is getting out of time. How could I do ?

# This returns the list of all the paintings of Wikidata 
# with an image 

SELECT ?item ?itemLabel ?image ?depicts ?Titre ?createur  ?itemDescription  (YEAR(?date) as ?year)  ?countryLabel  WHERE {
  ?item wdt:P31 wd:Q3305213. # Select only the paintings
  ?item wdt:P18 ?image. # Select only the element with an image
  OPTIONAL { ?item wdt:P17 ?country . }
  OPTIONAL { ?item wdt:P571 ?date . }
  OPTIONAL { ?item wdt:P571 ?itemDescription . }
  OPTIONAL { ?depicts rdfs:label ?dL	filter (lang(?dL) = "[AUTO_LANGUAGE]")  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]" }
} 

Thanks in advance.  – The preceding unsigned comment was added by NicaoGr (talk • contribs) at 22. 12. 2017, 13:47‎ (UTC).

# This returns the list of all the paintings of Wikidata 
# with an image 

SELECT ?item ?itemLabel ?image ?itemDescription (YEAR(?date) as ?year) ?countryLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q3305213. # Select only the paintings
  ?item wdt:P18 ?image. # Select only the element with an image
  OPTIONAL { ?item wdt:P17 ?country } .
  OPTIONAL { ?item wdt:P571 ?date } .
  #OPTIONAL { ?depicts rdfs:label ?dL FILTER( LANG( ?dL ) = "fr" ) } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]" } .
}

Try it!

Matěj Suchánek (talk) 14:13, 22 December 2017 (UTC)

BIND COALESCE issue

In the following, how do I BIND(COALESCE(?name2, ?name) as ?name3) so that it works (i.e. ?name3 takes the value of ?name2 if it exists, else takes ?name).

SELECT ?item ?name ?name2 ?name3 ?constituency ?party ?gender ?birth_date ?source WHERE {

 ?item p:P39 ?mem  .
 ?mem ps:P39 wd:Q18607856 ; pq:P2937 wd:Q45380990 .
 OPTIONAL {?item p:P973 ?url .
 ?url pq:P1810 ?name2 .
 }

 VALUES ?v {wd:Q7532178 wd:Q45383152}.
 FILTER(?item=?v).
 FILTER NOT EXISTS { ?mem pq:P582 [] . }

 OPTIONAL { ?mem pq:P4100|pq:P102 ?group . }
 OPTIONAL { ?mem pq:P768 ?area . }
 OPTIONAL { ?item wdt:P21 ?genderItem . }
 OPTIONAL { ?item wdt:P569 ?DoB . }
 OPTIONAL {  
   ?mem prov:wasDerivedFrom ?ref1 .
   OPTIONAL { ?ref1 pr:P854 ?source . }
 }  
 BIND(substr(str(?DoB),1,10) as ?birth_date)
 
 SERVICE wikibase:label { 
   bd:serviceParam wikibase:language "en" .
   ?item rdfs:label ?name .
   ?area rdfs:label ?constituency .
   ?group rdfs:label ?party .
   ?genderItem rdfs:label ?gender .
 }
 BIND(COALESCE(?name2,?name) as ?name3) 
}

Try it!

... thanks --Tagishsimon (talk) 16:57, 23 December 2017 (UTC)

SELECT ?item ?itemLabel ?name ?name2 ?name3 ?constituencyLabel ?partyLabel ?genderLabel ?birth_date ?source WHERE {
  VALUES ?item { wd:Q7532178 wd:Q45383152 }
  ?item p:P39 ?mem .
  ?mem ps:P39 wd:Q18607856; pq:P2937 wd:Q45380990 .
  FILTER NOT EXISTS { ?mem pq:P582 [] }
  
  OPTIONAL { ?item p:P973/pq:P1810 ?name2 }
  OPTIONAL { ?item wdt:P21 ?gender }
  OPTIONAL { ?item wdt:P569 ?DoB . BIND(SUBSTR(STR(?DoB), 1, 10) AS ?birth_date) }
  OPTIONAL { ?mem pq:P4100|pq:P102 ?party }
  OPTIONAL { ?mem pq:P768 ?constituency }
  OPTIONAL { ?mem prov:wasDerivedFrom [ pr:P854 ?source ] }
  
  ?item rdfs:label ?name . FILTER(LANG(?name) = 'en') .
  BIND(COALESCE(?name2, ?name) AS ?name3) .

  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Please have a look how the label service works: it creates ?…Label and ?…Description variables for each variable ?… with items or properties bound, but the variables are only good for output (i.e. you cannot access them inside the query). You do not need to tell it which variables to look for. That said, rdfs:label works differently, does not belong into the label service brackets, and in your query it is only necessary for the ?name variable which you subsequently use in the BIND() statement. Please note that I optimized a couple of further things. —MisterSynergy (talk) 17:27, 23 December 2017 (UTC)

Thank you, MisterSynergy, I'm very much obliged. I'll study all of the changes; and I'm grateful for the explanation & pointer to the manual ... I now grok the difference between the ?…Label and a value useful within the query; was previously oblivious to the distinction :) --Tagishsimon (talk) 19:41, 23 December 2017 (UTC)

Time units

Hello, I'm working on the values used for time in space (P2873) and I realized that some values have been entered using "seconds" as units instead of the commonly used "minutes". I wonder how a query could find all the values entered in a unit different from "minute". Thanks in advance! --FabC (talk) 16:08, 27 December 2017 (UTC)

SELECT ?item ?itemLabel ?unit ?unitLabel
WHERE 
{
  ?item p:P2873/psv:P2873 ?value.
  ?value wikibase:quantityUnit ?unit.
  FILTER(?unit != wd:Q7727)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Shinnin (talk) 16:13, 27 December 2017 (UTC)

Extremely fast! Many thanks --FabC (talk) 13:58, 28 December 2017 (UTC)

Filtering string equality

I tried to find minerals with ca labels ending in "a". My first approach didn't work and I would like to know why:

SELECT ?item ?label_ca WHERE {
  ?item wdt:P279* wd:Q7946.
  ?item rdfs:label ?label_ca.
  FILTER((LANG(?label_ca)) = "ca")
  #This doesn't work:
  FILTER(SUBSTR(?label_ca, STRLEN(?label_ca)) = "a")
}
LIMIT 1000

Try it!

Using STRENDS it worked:

SELECT ?item ?label_ca WHERE {
  ?item wdt:P279* wd:Q7946.
  ?item rdfs:label ?label_ca.
  FILTER((LANG(?label_ca)) = "ca")
  #This works:
  FILTER(STRENDS(?label_ca,"a"))
}
LIMIT 1000

Try it!

--Ssola (talk) 15:34, 29 December 2017 (UTC)

Labels in your query are literals, i.e. they have the form "label"@ca. It works if you do FILTER(SUBSTR(STR(?label_ca), STRLEN(?label_ca)) = "a") in the first example. See here how the STR() function converts literals to simple literals. —MisterSynergy (talk) 15:47, 29 December 2017 (UTC)

Oh, "a"@ca works as well. Thank you! There's a good explanation here too.--Ssola (talk) 17:20, 29 December 2017 (UTC)

List of Oil & Gas Companies

Hi Everyone,

I was wondering if anyone can help me with a query i am trying to run via Query Service. I would like to get a dataset of all Oil & Gas companies and their name, subsidiaries, location, total assets, total reserves. Eventually i would also like to get a list of oil and gas service providers with the same parameters/columns.

Anyway anyone can help me build this query or guide me on how to create it? I have tried the query below but the results were very small and not consistent (different result set every time i run the same query) or no values where returned to most of the parameters/columns i requested.

thanks again for your help

SELECT ?petroleum_industryLabel ?subsidiaryLabel ?industryLabel ?countryLabel ?total_assets WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?petroleum_industry wdt:P452 wd:Q862571.
  OPTIONAL { ?petroleum_industry wdt:P452 ?industry. }
  OPTIONAL { ?petroleum_industry wdt:P17 ?country. }
  OPTIONAL { ?petroleum_industry wdt:P355 ?subsidiary. }
  OPTIONAL { ?petroleum_industry wdt:P2403 ?total_assets. }
  OPTIONAL { ?petroleum_industry wdt:P17 ?country. }
}

Try it!

I fear we lack the data to support your query. We have only 84 items for which

⟨ ?item ⟩ industry (P452) ⟨ petroleum industry (Q862571)    ⟩

. There's probably some milage in trying to find items that have a parent which is in the oil business, as well as items listed as subsidiaries of items that are in the oil biz. I've tried to do that, below, but I caution that I'm mainly at the stage of throwing SPARQL at the wall to see if it sticks. Not least, there are many duplicate items in the results below, arising out of the columns selected. And my use of ?item wdt:P31/wdt:P279* wd:Q43229 . might, for all I know, be overly restrictive. Anyway, hth, a little.

SELECT  ?item ?itemLabel ?industryLabel ?countryLabel ?subsidiaryLabel ?total_assets 
WHERE {
  # get items whose parent is in the petroleum business
  { ?item wdt:P31/wdt:P279* wd:Q43229 .
    ?item wdt:P749 ?parent .
    ?parent wdt:P452 wd:Q862571 .
  }
UNION
  # get items which are in the petroleum business
  {
    ?item wdt:P452 wd:Q862571 .
  }
UNION
  # get items that are subsidiaries of items that are the petroleum business
  {
    ?parent wdt:P452 wd:Q862571.
    ?parent wdt:P355 ?item.
  }
 
  OPTIONAL { ?item wdt:P452 ?industry. }
  OPTIONAL { ?item wdt:P17 ?country. }
  OPTIONAL { ?item wdt:P355 ?subsidiary. }
  OPTIONAL { ?item wdt:P2403 ?total_assets. }
  
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 17:02, 30 December 2017 (UTC)

As a follow-up, I've added P452|Q862571 to a few hundred more items, finding them through https://petscan.wmflabs.org/?psid=2085588 and adding the property via https://tools.wmflabs.org/quickstatements/# ... this is a game you can join in with... --Tagishsimon (talk) 19:49, 30 December 2017 (UTC)

Thank you so much for the help. I think this is a great start. i am disappointed it doesn't have assets, revenue or equity for most of the listings but its not bad. I will use that to start with and go from there.

Thanks again for the help.

Hi There. Thanks again for your help with this. I Was wondering if I can bother you again with this new query? Is it possible to get a list of all Oil & Gas Services companies with their total assets as well? pretty much this list with the total assets associated if possible. https://en.wikipedia.org/wiki/List_of_oilfield_service_companies Thank you so much as this is saving me a lot of manual work to get this data.

FILTER NOT EXIST this OR that

How do I implement the filters in this query such that I get items where either ?hos or ?hog is null?

SELECT ?item ?itemLabel ?hogLabel ?hosLabel
WHERE 
{
  ?item wdt:P31 wd:Q6256.
  OPTIONAL {?item wdt:P1906 ?hos .}
  OPTIONAL {?item wdt:P1313 ?hog .}
    
  FILTER NOT EXISTS {?item wdt:P1906 [] .}
  # FILTER NOT EXISTS {?item wdt:P1313 []  .}
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?item

Try it!

thanks --Tagishsimon (talk) 01:38, 31 December 2017 (UTC)

SELECT ?item ?itemLabel ?hogLabel ?hosLabel WHERE {
  ?item wdt:P31 wd:Q6256.
  OPTIONAL { ?item wdt:P1906 ?hos }
  OPTIONAL { ?item wdt:P1313 ?hog }
  FILTER(!BOUND(?hos)|| !BOUND(?hog)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ?item

Try it!

—MisterSynergy (talk) 08:06, 31 December 2017 (UTC)

Thank you, MisterSynergy. Such an odd language, SPARQL ;) --Tagishsimon (talk) 10:56, 31 December 2017 (UTC)