One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified? Let’s measure! How does COUNT(…) work? But … Continue reading What’s Faster?…
#aggregate function
5 posts
19 Sept 2019
9 Oct 2018
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP() VAR_SAMP() ARRAY_AGG() STRING_AGG() But what if you want to roll your own? Java 8 Stream Collector When using Java 8 streams, we can easily roll our own aggregate function (i.e. a … Continue reading Writing Custom…
21 Sept 2018
Everyone knows the SQL SUM() aggregate function (and many people also know its window function variant). When querying the Sakila database, we can get the daily revenue (using PostgreSQL syntax): The result will look something like this: date |daily_revenue |cumulative_revenue -----------|--------------|------------------- 2005-05-24 |29.92 |29.92 2005-05-25 |573.63 |603.55 2005-05-26 |754.26 |1357.81 2005-05-27 |685.33 |2043.14 2005-05-28 |804.04 … Continue reading How to…
20 Apr 2017
At a customer site, I’ve recently encountered a report where a programmer needed to count quite a bit of stuff from a single table. The counts all differed in the way they used specific predicates. The report looked roughly like this (as always, I’m using the Sakila database for illustration): And then, unsurprisingly, combinations of … Continue reading How to…
18 Dec 2014
We’ve just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended). Let’s assume we have four books in our table: Now the question is: Is EVERY() ID lower … Continue reading A True…