Is there any equivalent of the Postgresql aggregation function EVERYONE on other DBMSs?

Question

Documentation for EACH unit:

each (expression) : true if all input values ​​are true, otherwise false

http://www.postgresql.org/docs/9.1/static/functions-aggregate.html

---

EVERY semantically equivalent to COUNT (conditionIsTrue) = COUNT (*)

select person_id, every(visited_site = 'http://stackoverflow.com') as visited_same_site_forever, count(case when visited_site = 'http://stackoverflow.com' then '^_^' end) = count(*) as visited_same_site_forever2 from z group by person_id order by person_id 

Conclusion:

  person_id | visited_same_site_forever | visited_same_site_forever2 -----------+---------------------------+---------------------------- 88 | f | f 55327 | t | t 256196 | f | f 

Data source:

 create table z(person_id int, visited_site varchar(100), datetime_visited timestamp); insert into z values (55327,'http://stackoverflow.com','Jan 1, 2010'), (55327,'http://stackoverflow.com','Feb 14, 2012'), (55327,'http://stackoverflow.com','May 1, 2012'), (256196,'http://stackoverflow.com','February 1, 2012'), (256196,'http://stackoverflow.com','February 2, 2012'), (256196,'http://slashdot.org','May 2, 2012'), (88,'http://theregister.co.uk','April 1, 2012'), (88,'http://slashdot.org','April 2, 2012'); 

Answer 1

Emulating EVERY() with CASE and SUM()

In fact , this article describes how EVERY() can be emulated through CASE and SUM() . The following two statements are equivalent:

 SELECT EVERY(id < 10) FROM book SELECT CASE SUM(CASE WHEN id < 10 THEN 0 ELSE 1 END) WHEN 0 THEN 1 ELSE 0 END FROM book; 

The same is true for the EVERY() window function:

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

SQL standard

The SQL:2008 standard refers to the aggregate function EVERY :

 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> ] <general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren> <set function type> ::= <computational operation> <computational operation> ::= AVG | MAX | MIN | SUM | EVERY | [...] 

But the "extended" standard SQL functions are not often implemented by databases. Oracle 11g , for example, does not support it, and SQL Server 2012 .

With HSQLDB , however, you might be more fortunate. HSQLDB 2.x is very standards compliant, MySQL also knows the aggregation function BIT_AND() , which is the non-standard alias EVERY() , also supported by Postgres.

Please note: some databases allow you to write custom aggregated functions, so you can also implement EVERY() yourself.