SPARQL wikidata group-by: get grouped items in the answer (rivers with multiple cities)

user_333403 0
Viewed 47

The following query returns rivers with at least 3 big cities on them.

try it on wikidata query service

# rivers with at least 3 big cities

SELECT ?river ?riverLabel (COUNT(?city) AS ?citycount)
WHERE {
  ?river wdt:P31 wd:Q4022. # ... is a river
  ?city wdt:P31 wd:Q1549591. # ... is a big city
  ?city wdt:P206 ?river.  # ... located in or next to body of water
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]".}
}
GROUP BY ?river ?riverLabel
HAVING (?citycount >= 3)    # ← important line
ORDER BY DESC(?citycount)

How can I include the actual cities (and their labels) in the answer? The ordering should stay the same, i.e. the first 13 results should be the cities located at Yangtze river, the next 11 results those at Rhine etc.

wikidata-query-service sparql aggregation
1 Answers

You should try using GROUP_CONCAT, see below.

# rivers and cities
SELECT ?river ?riverLabel (COUNT(?city) AS ?citycount)
(GROUP_CONCAT(CONCAT(STR(?city), "--> ", ?cityLabel); SEPARATOR="; ") AS ?cities)
WHERE {
  ?river wdt:P31 wd:Q4022. # ... is a river
  ?city wdt:P31 wd:Q1549591. # ... is a big city
  ?city wdt:P206 ?river.  # ... located in or next to body of water
  ?city rdfs:label ?cityLabel .
  FILTER(LANG(?cityLabel) = 'en')
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]".}
}
GROUP BY ?river ?riverLabel
HAVING (?citycount >= 3)
ORDER BY DESC(?citycount)
user_133890860
Related