SPARQL GROUP BY: Counting Values Per Group with Aggregations

  

In previous lessons, we learned how aggregation functions such as COUNT, SUM, AVG, MIN, and MAX can help us summarize data.

 

However, aggregations become much more powerful when combined with GROUP BY. A common requirement when querying RDF data is not to count everything in the dataset, but to count things per entity, per category, or per relationship.

 

For example:

·      How many superheroes belong to each team?

·      How many movies does each character appear in?

·      How many villains has each team fought against?

 

To answer questions like these, we need a way to partition our results into groups before applying aggregation functions. This is exactly what the GROUP BY clause provides.

 

In this post, we'll learn how to use GROUP BY together with COUNT to calculate the number of superheroes in each Marvel team.

 

Let's start with a simple question, How many superheroes belong to each team?

 

Before we can count anything, let's first inspect the underlying data.

 

The following query retrieves each person and the team they belong to.

PREFIX : <http://example.org/marvel/>

SELECT ?personName ?teamName
WHERE {
    ?person a :Person ;
            :name ?personName ;
            :memberOf ?team .

    ?team :name ?teamName .
}

 

Output

personName	teamName
Bruce Banner	Avengers
Natasha Romanoff	Avengers
Steve Rogers	Avengers
Tony Stark	Avengers
Peter Quill	Guardians of the Galaxy

 

From the results, we can already observe that:

 

Avengers has 4 members

Guardians of the Galaxy has 1 member

 

But our goal is not to manually count them. We want SPARQL to do the counting for us.

PREFIX : <http://example.org/marvel/>

SELECT ?teamName (COUNT(?person) AS ?memberCount)
WHERE {
    ?person a :Person ;
            :memberOf ?team .

    ?team :name ?teamName .
}
GROUP BY ?teamName

   

Output

teamName	memberCount
Avengers	4
Guardians of the Galaxy	1

 

Now SPARQL first groups all rows by ?teamName and then applies COUNT(?person) separately to each group.

 

In summary,

·      Aggregation functions summarize data across multiple rows.

·      GROUP BY divides results into groups before aggregation occurs.

·      COUNT() is commonly used with GROUP BY to count items per group.

·      Every non-aggregated variable in the SELECT clause should typically appear in the GROUP BY clause.

·      Without GROUP BY, SPARQL cannot determine how aggregation should be partitioned across different entities.

 

Think of GROUP BY as saying "First organize the matching rows into buckets, then perform the aggregation separately for each bucket".

Previous                                                    Next                                                    Home