---
title: Aggregation
url: https://docs.triply.cc/concepts/sparql-aggregation/
description: Counting, summing and grouping in SPARQL, and the implicit grouping rules that quietly return
  the wrong table.
---

# 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](https://docs.triply.cc/concepts/sparql-basics/) and
[graph patterns](https://docs.triply.cc/concepts/sparql-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

```sparql
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.

```sparql
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.

```sparql
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

<!-- TODO — EXAMPLE NEEDED
     This section describes the technique but shows no query. Add a working
     nested-aggregate query against the academy/pokemon dataset: count names per
     Pokémon in a subselect, then take max() of that count in the outer query.
     Verify it runs before publishing. Together with the section below, this is
     the hardest material on the page and it is the one place where prose alone
     is not enough. -->

"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

<!-- TODO — EXAMPLE NEEDED
     This section describes the technique but shows no query. Add a working query
     against the academy/pokemon dataset: compute max(?weight) in a subselect,
     then match in the outer query for the Pokémon whose weight equals it, so the
     result carries both the number and the resource. Verify it runs before
     publishing. -->

`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:

```sparql
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:

```sparql
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](https://docs.triply.cc/reference/data-views/#insights).
- **Data stories** commonly use aggregating queries to drive charts.
  See [Data stories](https://docs.triply.cc/how-to/data-stories/).

## Where to next

- [Construct, ask, describe and federation](https://docs.triply.cc/concepts/sparql-construct-and-federation/) —
  the other three query forms
- [Querying real-world data](https://docs.triply.cc/concepts/sparql-real-world-data/) — hierarchies, geodata and
  statistics
- [Graph patterns](https://docs.triply.cc/concepts/sparql-graph-patterns/) — back one chapter
- [SPARQL: the basics](https://docs.triply.cc/concepts/sparql-basics/) — back to the first chapter
- [Glossary](https://docs.triply.cc/reference/glossary/) — every term on this page, in one place