In the recent past, we’ve explained the syntactic implications of the SQL GROUP BY clause. If you haven’t already, you should read our article “
Do You Really Understand SQL’s GROUP BY and HAVING clauses?“.
In essence, adding a
GROUP BY clause to your query transforms your query on very implicit levels. The following reminder summarises the previous article:
- Only column expressions referenced in the
GROUP BY clause, or aggregations of other column expressions may appear in the SELECT clause
- Aggregations without explicit
GROUP BY clause imply the “grand total” GROUP BY () clause
- Some databases (e.g. MySQL, and to some extent: the SQL standard) don’t follow these rules and allow for arbitrary column expressions (or at least functionally dependent column expressions) in the
SELECT clause
How SQL GROUP BY should have been designed
There is another way of looking at
GROUP BY, and it has been implemented in the equally
fascinating, beautiful, and weird Cypher query language (those are good attributes) as supported by the
Neo4j graph database. This alternative (yet SQL inspired) query language probably deserves a whole blog post series on its own, but let’s focus on aggregation. Because aggregation is the primary use case for grouping.
(for the record, check out the Neo4j docs about aggregation for details)
A quick wrap-up to understand Cypher:
Consider this simple Cypher query:
MATCH (me:Person)-->(friend:Person)
-->(friend_of_friend:Person)
WHERE me.name = 'A'
RETURN count(DISTINCT friend_of_friend),
count(friend_of_friend)
Furthermore
Cypher’s way of writing
JOIN is actually extremely useful and could also be applied to SQL. It is only a matter of time until someone will write a Cypher-to-SQL transformer that implements the syntax, at least as syntactic sugar for the equivalent ANSI equi-join notation.
Let’s investigate aggregation in Cypher
Here’s the query again:
MATCH (me:Person)-->(friend:Person)
-->(friend_of_friend:Person)
WHERE me.name = 'A'
RETURN count(DISTINCT friend_of_friend),
count(friend_of_friend)
So, in SQL terms, this is exactly the same as:
SELECT count(DISTINCT friend_of_friend),
count(friend_of_friend)
FROM [ Persons ... ]
WHERE me.name = 'A'
In other words, the same implicit grand total
GROUP BY () is implied and all values are aggregated into a single row.
The
next example from the Neo4j docs is more intriguing. This will count the number of nodes connected to a node
n with
name = 'A':
MATCH (n { name: 'A' })-->(x)
RETURN n, count(*)
Which is a shorter form for writing:
MATCH (n)-->(x)
WHERE n.name = 'A'
RETURN n, count(*)
This example will also perform aggregation, but this time with an implicit
GROUP BY n clause. In SQL, you’d write something like:
SELECT n.id, count(*)
FROM n
JOIN x
ON [ implicit equi-join predicate ]
WHERE n.name = 'A'
GROUP BY n.id
The nice thing in Cypher is that the obvious
GROUP BY clause (it can only be
GROUP BY n.id) is implied. It doesn’t have to be written explicitly.
Takeaway for SQL
We’ve seen a couple of nice Cypher language features, especially the incredibly nice way to write “JOIN” (or rather graph traversal in Neo4j). But a much more obvious, low-hanging fruit with actual chances to make it into the SQL standard would be to make the SQL
GROUP BY clause optional, and dependent on the
SELECT clause using the following rules:
- If
SELECT contains no aggregation functions, there shall be no implied GROUP BY clause
- If
SELECT contains 1-N aggregation functions, there shall be an implied GROUP BY clause formed from the remaining columns
- If
SELECT contains only aggregation functions, the “grand total” GROUP BY () shall apply
- An explicit
GROUP BY clause will always be preferred to any implied GROUP BY clause
If any of you ISO / IEC committee members are reading this, this is on my wish list for a future SQL standard. And please, PostgreSQL. Implement this right away.
Liked this article?
Here’s some further reading about the SQL
GROUP BY clause and aggregation:
