Aggregates and GROUP BY

COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT and SAMPLE, with GROUP BY and HAVING.

Examples use the sample data.

Count per group

Triples per predicate:

SELECT * FROM pgrdf.sparql($$
  SELECT ?p (COUNT(?o) AS ?n)
  WHERE { ?s ?p ?o }
  GROUP BY ?p
  ORDER BY DESC(?n) ?p
$$);
--  {"n": "3", "p": "http://example.org/age"}
--  {"n": "3", "p": "http://www.w3.org/1999/02/22-rdf-syntax-ns#type"}
--  {"n": "3", "p": "http://xmlns.com/foaf/0.1/knows"}
--  {"n": "3", "p": "http://xmlns.com/foaf/0.1/name"}
--  {"n": "2", "p": "http://xmlns.com/foaf/0.1/mbox"}

Like every value in pgrdf.sparql() results, counts come back as strings ("3"). Cast in SQL when you need a number: (sparql->>'n')::int.

Aggregates over the whole result

Without GROUP BY, the whole result is one group:

SELECT * FROM pgrdf.sparql($$
  PREFIX ex: <http://example.org/>
  SELECT (COUNT(*) AS ?people) (SUM(?age) AS ?total) (AVG(?age) AS ?avg)
         (MIN(?age) AS ?youngest) (MAX(?age) AS ?oldest)
  WHERE { ?p ex:age ?age }
$$);
--  {"avg": "34.6666666666666667", "total": "104", "oldest": "41", "people": "3", "youngest": "29"}

A group with no matches still counts: COUNT(*) over a pattern that matches nothing returns {"n": "0"}.

The functions

Function	Returns
COUNT(?v), COUNT(*), COUNT(DISTINCT ?v)	Number of bound values, of solutions, or of distinct values.
SUM(?v)	Sum of numeric values.
AVG(?v)	Average, as a decimal ("34.6666666666666667").
MIN(?v), MAX(?v)	Smallest or largest. Numbers compare by value (9 comes before 29); strings compare alphabetically.
GROUP_CONCAT(?v; SEPARATOR=", ")	Values joined into one string. The default separator is a space; DISTINCT is allowed.
SAMPLE(?v)	Any one value from the group.

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT ?p (GROUP_CONCAT(?fname; SEPARATOR=", ") AS ?friends)
            (SAMPLE(?fname) AS ?one)
            (COUNT(DISTINCT ?f) AS ?n)
  WHERE { ?p foaf:knows ?f . ?f foaf:name ?fname }
  GROUP BY ?p
  ORDER BY ?p
$$);
--  {"n": "2", "p": "http://example.org/alice", "one": "Bob", "friends": "Carol, Bob"}
--  {"n": "1", "p": "http://example.org/bob", "one": "Carol", "friends": "Carol"}

Over UNION

Aggregates work over a UNION. Here ?x is counted once per solution from either branch (three names plus two mailboxes):

SELECT * FROM pgrdf.sparql($$
  PREFIX foaf: <http://xmlns.com/foaf/0.1/>
  SELECT (COUNT(?x) AS ?n)
  WHERE { { ?x foaf:name ?name } UNION { ?x foaf:mbox ?mbox } }
$$);
--  {"n": "5"}

To keep only some groups, add HAVING.