`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:
```
INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');
```

`Is ``EVERY()`

ID lower than 10?

We’ll ask:
```
SELECT EVERY(id < 10)
FROM book
```

every ----- true

`Does ``EVERY()`

book for each author end with the letter ‘a’?

We’ll ask:
```
SELECT author_id, EVERY(title LIKE '%a')
FROM book
GROUP BY author_id
```

author_id every ----------------- 1 false 2 trueWonderful! As with all aggregate functions, we can even use them as a window function!

```
SELECT
book.*,
EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book
```

id author_id title every ------------------------------------ 1 1 1984 false 2 1 Animal Farm false 3 2 O Alquimista true 4 2 Brida true

## Who supports `EVERY()`

Well, the SQL standard has it:
10.9 <aggregate function> <aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ] | <binary set function> [ <filter clause> ] | <ordered set function> [ <filter clause> ] | <array aggregate function> [ <filter clause> ] <general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren> <set function type> ::= <computational operation> <computational operation> ::= AVG | MAX | MIN | SUMAnd, of course PostgreSQL! But if your database is not PostgreSQL, don’t worry.| EVERY <-- yes, here! EVERY!| ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | COLLECT | FUSION | INTERSECTION

`EVERY()`

can be emulated on `EVERY()`

database using `SUM()`

and `CASE`

expressions. Here’s how to emulate the first query:
```
-- SELECT EVERY(id < 10)
-- FROM book
SELECT MIN(CASE WHEN id < 10 THEN 1 ELSE 0 END)
FROM book;
```

```
-- SELECT
-- book.*,
-- EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
-- FROM book
SELECT
book.*,
MIN(CASE WHEN title LIKE '%a' THEN 1 ELSE 0 END)
OVER(PARTITION BY author_id)
FROM book;
```

**For a more concise emulation, see Stew Ashton’s comments**And, as always on this blog, we’re happy to conclude that the upcoming jOOQ 3.6 will now handle

`EVERY(emulation)`

for you, so you can write:
```
DSL.using(configuration)
.select(BOOK.fields())
.select(every(BOOK.TITLE.like("%a"))
.over(partitionBy(BOOK.AUTHOR_ID)))
.from(BOOK)
.fetch();
```

A more concise emulation:

Sorry, I meant the following:

That’s a cool idea, thanks for sharing, Stew!

I ended up implementing this as an improvement:

I’ll also fix the article, because the original solution was not correctly aggregating empty sets to NULL

curious about what those COLLECT and FUSION are…

The standard says

“COLLECT (X) is equivalent to FUSION (MULTISET [X]).”.Unfortunately, this is the definition of FUSION:

If FUSION is specified, then the result is the multiset M such that for each value V in the element type of DT, including the null value, the number of elements of M that are identical to V is the sum of the number of identical copies of V in the multisets that are the values of the column in each row of TXA.So, we aren’t really any smarter ;-)

On a more serious note. These functions aggregate groups into nested collections (“multisets”), similar to what ARRAY_AGG does with arrays.

So, we can forgive those who didn’t take the time to implement them ;-)

There are much more forgivable omissions from the SQL standard by the implementations… ;)

If the DB allows MIN and MAX on BOOLEAN values (with FALSE < TRUE), then EVERY is equivalent to MIN, and SOME/ANY are equivalent to MAX.

There’s no need for a CASE expression to convert BOOLEAN to INTEGER.

SELECT

min(id < 10) AS bool_and, — a.k.a. EVERY()

max(id < 10) AS bool_or

FROM book;

This works on hsqldb and Calcite. I haven’t tried any other DBs.

That’s clever. And I’ll be damned, I was going to point out that no way this is SQL standards compliant, but it is. 8.2 <comparison predicate> General Rules 8)

Can’t say I really like this, but it does make sense.