On this page:

SPARQL: aggregation

So far every query has returned one row per match. Aggregation collapses many rows into one: how many, how much, the largest, the whole list as a single value.

This is the chapter where SPARQL surprises people. The grouping rules are implicit by default, and when they misfire the query still runs and still returns a table, just not the one you meant. This chapter spends most of its length on that.

This chapter assumes SPARQL: the basics and graph patterns.

What aggregation does

An aggregation is a function applied to a group of values. The groups are formed by taking every unique combination of values for a chosen set of variables.

An example makes this concrete. In the Pokémon dataset, each Pokémon has a name in several languages, so a plain query returns several rows per Pokémon:

?pokemon ?name
id:abomasnow "ABOMASNOW"@it-it
id:abomasnow "BLIZZAROI"@fr-fr
id:abomasnow "ユキノオー"@ja-ja
id:abra "ABRA"@en-us
id:abra "ケーシィ"@ja-ja

Group by ?pokemon and the set of names for each Pokémon becomes one group. You can then count them, list them, or take the longest.

Counting, explicitly

prefix vocab: <https://triplydb.com/academy/pokemon/vocab/>

select ?pokemon (count(?name) as ?numberOfNames)
where {
  ?pokemon vocab:name ?name.
}
group by ?pokemon
limit 25

Two things changed. The projection now contains an expression, which needs the as ?variable form to give the column a name. And group by states the grouping criterion.

Besides count, the standard functions are sum, avg, min, max, sample (any one value from the group), and group_concat.

Grouping that you did not ask for

Here is the part to slow down for. If a query contains an aggregation function and no group by, SPARQL groups anyway. It groups by every variable that is (1) visible in the projection and (2) not an argument to an aggregation function.

Leave out group by ?pokemon from the query above and you get the same result, because ?pokemon is visible and is not inside count(). That is convenient, and it is also the trap: add one more variable to the projection and the grouping silently changes underneath you.

select ?pokemon ?colour (count(?name) as ?numberOfNames)
where {
  ?pokemon
    vocab:name ?name;
    vocab:colour ?colour.
}

This no longer counts names per Pokémon. It counts names per combination of Pokémon and colour. With one colour per Pokémon the numbers happen to match; with two, they quietly halve.

The rule to remember: the projection determines the grouping. Adding a column is never cosmetic in an aggregating query.

Hiding a grouping variable

Grouping variables have to appear in the projection. So what do you do when you need to group by something you do not want as a column?

You cannot simply drop it. You need a subselect: an inner query that does the grouping, wrapped by an outer query that projects only what you want.

prefix vocab: <https://triplydb.com/academy/pokemon/vocab/>

select ?numberOfNames
where {
  {
    select ?pokemon (count(?name) as ?numberOfNames)
    where {
      ?pokemon vocab:name ?name.
    }
    group by ?pokemon
  }
}

The inner query groups by ?pokemon; the outer query returns only the counts. Subselects are the standard answer whenever grouping and projection want different things.

Aggregating an aggregate

"Which Pokémon has the most names?" needs two aggregations in sequence: first count per Pokémon, then take the maximum of those counts. Since an aggregation function cannot take another aggregation as its argument, this is a subselect again — the inner query counts, the outer query takes the maximum.

Returning the evidence

max(?weight) gives you a number, not the thing that weighs it. This is a recurring frustration: you want the heaviest Pokémon, and SPARQL hands you the weight.

The pattern is the same subselect shape. Compute the maximum in an inner query, then match in the outer query for the resource whose value equals it. The result carries both the number and the evidence for it.

Building one string from many rows

group_concat turns a group of values into a single string, with a separator you choose:

prefix vocab: <https://triplydb.com/academy/pokemon/vocab/>

select ?pokemon (group_concat(?name; separator=', ') as ?names)
where {
  ?pokemon vocab:name ?name.
}
group by ?pokemon
limit 25

This is how you get one readable row per resource instead of five near-identical ones — useful for exports and for anything a human will read.

Filtering on the result of an aggregation

filter runs before grouping, so it cannot test a count. having is the version that runs after:

prefix vocab: <https://triplydb.com/academy/pokemon/vocab/>

select ?pokemon (count(?name) as ?numberOfNames)
where {
  ?pokemon vocab:name ?name.
}
group by ?pokemon
having (count(?name) > 5)

If you find yourself wondering why a filter on an aggregated column is ignored, this is why.

Where this appears in Triply products

  • TriplyDB's Insights page is aggregation made visual: the class frequency view counts how often each class and property occurs in a graph. See Data views.
  • Data stories commonly use aggregating queries to drive charts. See Data stories.

Where to next