Skip to main content

Aggregations & Grouping

Group query results and compute aggregate values like counts, sums, and averages.

groupBy​

required?type
noarray or string

The groupBy clause lets you group query results by a predicate and perform aggregate operations on the groups.

For example, let's say you have a query that returns the name and company for all people in your database, and the results look like this:


[
{
"ex:company": "Fluree",
"ex:name": "Letitia"
},
{
"ex:company": "Fluree",
"ex:name": "Freddy"
},
{
"ex:company": "Beep Boop",
"ex:name": "Daniel"
}
]

You could group these results by ex:company to see who works for each company:


{
"@context": { "ex": "http://example.org/" },
"select": ["?company", "?name"],
"where": {
"@id": "?s",
"ex:name": "?name",
"ex:company": "?company"
},
"groupBy": "?company"
}

This would return:


[
["Fluree", ["Letitia", "Freddy"]],
["Beep Boop", ["Daniel"]]
]

You can also perform aggregate operations on the grouped data. These operations let you answers questions like, "how many people are employed at each company?" or "what is the lowest price that a product has sold for?"

If you wanted to get a count of people per employer, you could write a query like this:


{
"@context": { "ex": "http://example.org/" },
"select": ["?company", "(count ?s)"],
"where": {
"@id": "?s",
"ex:name": "?name",
"ex:company": "?company"
},
"groupBy": "?company"
}

And you would get results like this:


[
["Fluree", 2],
["Beep Boop", 1]
]

having​

required?type
noarray or string

Having lets you filter on grouped data using arbitrary expressions.

NOTE: having is only valid when used with groupBy.


{
"@context": { "ex": "http://example.org/", "schema": "http://schema.org/" },
"select": ["?name", "?favNums"],
"where": {
"@id": "?s",
"schema:name": "?name",
"ex:favNums": "?favNums"
},
"groupBy": "?name",
"having": "(>= (avg ?favNums) 10)"
}

As English, this query would read: "Find all entities with names and favorite numbers. Group the results by name. Keep only results where the average of all ?favNums for a ?name is greater than or equal to 10."

If we had used a filter function without groupBy, then we would have been filtering on the average of all favNum values across all entities. By using, groupBy & having, we can filter on the average of only those values grouped to a particular ?name.

Aggregate Functions​

Aggregate functions compute values across multiple results. They are used in the select clause and are typically combined with groupBy to compute aggregates per group.

Available Aggregates​

functiondescriptionexample
countCount of values(count ?s)
sumSum of numeric values(sum ?price)
avgAverage of numeric values(avg ?age)
minMinimum value(min ?date)
maxMaximum value(max ?salary)
sampleRandom sample from values(sample ?name)
group_concatConcatenate values into string(group_concat ?tag)

Count Example​


{
"select": ["(count ?s)"],
"where": { "@id": "?s", "@type": "ex:Person" }
}

Returns: [[42]] (if there are 42 Person entities)

Grouped Aggregates​


{
"select": ["?department", "(count ?employee)", "(avg ?salary)"],
"where": {
"@id": "?employee",
"ex:department": "?department",
"ex:salary": "?salary"
},
"groupBy": "?department"
}

Returns results like:


[
["Engineering", 25, 95000],
["Sales", 15, 75000],
["Marketing", 10, 70000]
]

Multiple Aggregates​

You can use multiple aggregate functions in a single query:


{
"select": ["(count ?s)", "(sum ?price)", "(avg ?price)", "(min ?price)", "(max ?price)"],
"where": { "@id": "?s", "ex:price": "?price" }
}

Next Steps​