SPARQL/Bad aggregate

From Wikibooks, open books for an open world
Jump to navigation Jump to search

The error Bad aggregate might appear when coding mistakes upon using GROUP BY. See also the chapter modifiers for a description of GROUP BY.

The error "Bad aggregate" does not specify what exactly is the mistake, hence this explanation to help you find out the cause.

This is a correct example. This list the children of Bach, grouped by their mother:

SELECT ?mother ?motherLabel (COUNT(?child) AS ?children) 
        (GROUP_CONCAT(DISTINCT ?childLabel; SEPARATOR=", ") AS ?names)
  ?child wdt:P22 wd:Q1339.# ?child has father Bach
  ?child wdt:P25 ?mother.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". 
                          ?mother rdfs:label ?motherLabel.
                          ?child  rdfs:label ?childLabel.
GROUP BY ?mother ?motherLabel

Try it!

The first condition is that the grouping variables should match.

  • In the SELECT the grouping is by both variables ?mother and ?motherLabel.
  • In the GROUP BY the same variables ?mother ?motherLabel should be listed.

Try for yourself what happens when you use GROUP BY ?mother.
At the bottom of the long list of the errors you might find the text "Non-aggregate variable in select expression: motherLabel".
If the GROUP BY is left out completely the error message is similar "Non-aggregate variable in select expression: mother".

The second condition is that the rest of the variables in the SELECT should each result into one value.

  • You need expressions that will result into one value, for instance COUNT or one of the other Aggregate functions MIN, MAX, SUM or AVG.
  • You can also use GROUP_CONCAT to list all values into one variable, as all values of ?childLabel into ?names.
  • All variables should be converted with expressions as described above, even if a variable has one value only.

Try for yourself what happens when you change in the rest of the SELECT code.
If for instance you would use ?childLabel you might find at the bottom of the long list of the errors the text "Non-aggregate variable in select expression: childLabel".

The error message is always "Non-aggregate variable in select expression: xxxxx", whether a variable is missing from the GROUP BY or a variable not aggregated at the SELECT clause.