GROUP and COUNT in SPARQL

Question

I am using WikiData SPARQL Endpoint to search for two entities: museums (wd:Q33506) and libraries (wd:Q7075) that have instagram and twitter accounts and from which country they are from. I am trying to count them and group them by country.

SELECT ?item ?instagram ?twitter ?countryLabel (COUNT(?country) AS ?ccount) WHERE {
  { ?item (wdt:P31/wdt:P279*) wd:Q33506. }
  { ?item (wdt:P31/wdt:P279*) wd:Q7075. }
  ?item wdt:P2003 ?instagram.
  ?item wdt:P2002 ?twitter.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?item wdt:P17 ?country. }
}
GROUP BY ?country
ORDER BY ?ccount

Stanislav Kralin · Accepted Answer · 2018-08-13T18:27:55.630

The main error is:

Query is malformed: Bad aggregate

From 11.4 Aggregate Projection Restrictions:

In a query level which uses aggregates, only expressions consisting of aggregates and constants may be projected, with one exception. When GROUP BY is given with one or more simple expressions consisting of just a variable, those variables may be projected from the level.

Thus, your query should be:

SELECT ?countryLabel (COUNT(DISTINCT ?item) AS ?ccount) WHERE {
  { ?item wdt:P31/wdt:P279* wd:Q33506. }
  UNION
  { ?item wdt:P31/wdt:P279* wd:Q7075. }
  ?item wdt:P2003 ?instagram.
  ?item wdt:P2002 ?twitter.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?item wdt:P17 ?country. }
}
GROUP BY ?countryLabel ORDER BY DESC(?ccount)

Try it!

I've also added UNION, DISTINCT and replaced ?country with ?item in the projection.

Note that libraries and museums that have both Instagram and Twitter accounts are counted.

Ah, I see. I was trying to use empty GROUP. Then I should be able to add as many UNIONs to add other organisations — Trinker, Aug 13 '18 at 10:59
@Trinker please use `VALUES` instead of multiple `UNION` if you want to process a list of URIs — UninformedUser, Aug 13 '18 at 11:54

GROUP and COUNT in SPARQL

1 Answers1