How to Group By "Nothing" in SQL
How to Group By "Nothing" in SQL
This tutorial explains how to group "nothing" in SQL.
Join the DZone community and get the full member experience.Join For Free
New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by "nothing" in SQL. E.g. when querying the Sakila database:
SELECT count(*) FROM film GROUP BY ()
This will yield:
count | ------| 1000 |
What's the point, you're asking? Can't we just omit the
GROUP BY clause? Of course, this will yield the same result:
SELECT count(*) FROM film
Yet, the two versions of the query are subtly different. The latter will always return exactly one row. The former will perform grouping and return all the groups. How is this different? Just add a predicate!
SELECT count(*) FROM film WHERE 1 = 0 GROUP BY (); SELECT count(*) FROM film WHERE 1 = 0;
Now, the first query will produce nothing!
count | ------|
Whereas the second one produces:
count | ------| 0 |
Subtle, eh? Note that unlike DB2, Oracle and SQL Server (which expose the above behavior), PostgreSQL does not produce the above result as it seems to implement the SQL standard (so, always producing a row) as shown by Markus Winand:
IMHO, it's not PostgreSQL doing it wrong, but Oracle and SQL Server.
If GROUP BY is omitted "GROUP BY ()" can be implied (e.g., §7.16 SR16).
GROUP BY () puts all rows into a single group (§7.14 GR 2)
Every group is replaced by one row (§7.16 GR 1b ii)
(Refs to SQL-2:2016)
- Markus Winand (@MarkusWinand) May 25, 2018
In SQL:1999 (when it was introduced), the
<empty grouping set> was called
<grand total>, akin to a grand total that can be calculated in a Microsoft Excel Pivot Table. It does make more sense for grand totals to always be present in the result, despite the absence of any input data.
What if Your Database Doesn't Support Grouping Sets?
Not all databases support the awesome GROUPING SETS feature. Among the ones supported by jOOQ, these do:
Note that the following databases support a vendor-specific syntax for ROLLUP, which doesn't help with the empty grouping set.
So, can we emulate it for the other databases?
Of course. There are two ways to emulate the empty grouping set:
By Using a Constant
You could try using a constant literal:
SELECT count(*) FROM film WHERE 1 = 0 GROUP BY 'a';
Sometimes, you'll have to tweak the database into thinking it is not a constant literal because it will not accept that:
SELECT count(*) FROM film WHERE 1 = 0 GROUP BY 'a' || 'b';
And if that's also not supported, try wrapping the literal in a subquery:
SELECT count(*) FROM film WHERE 1 = 0 GROUP BY (SELECT 1);
One of the above three syntaxes is usually accepted, by these databases:
By Using a Dummy Table
In rare cases, none of the above works as the database's SQL parser tries to be "clever" and rejects my silly attempts to fool it, but no one can fool me!
Again, Microsoft SQL Data Warehouse — you cannot fool me with your lack of functionality. I want to GROUP BY () (the empty grouping set), and I will! pic.twitter.com/cYePMpL58I
- Lukas Eder (@lukaseder) May 25, 2018
I'll just cross join whatever is in the
FROM clause with a dummy table (akin to an emulation of table dee) and then group by the dummy table's column:
SELECT count(*) FROM film, (SELECT 1 x) dummy WHERE 1 = 0 GROUP BY dummy.x;
This is guaranteed to work, including on these databases:
(needless to say that jOOQ supports this emulation. You can play around with it here: https://www.jooq.org/translate)
Published at DZone with permission of Lukas Eder , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.