The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : PRODUCT | previous : next |
New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.3
PRODUCT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The PRODUCT()
aggregate function is a synthetic aggregate function that calculates the product of all values in the group, similar to how the SUM function calculates the sum.
SELECT product(ID) FROM BOOK
create.select(product(BOOK.ID)) .from(BOOK)
Producing:
+---------+ | product | +---------+ | 24 | +---------+
Dialect support
This example using jOOQ:
product(BOOK.ID)
Translates to the following dialect specific expressions:
-- ACCESS (SWITCH(sum(SWITCH(BOOK.ID = 0, 1)) > 0, 0, sum(SWITCH(BOOK.ID < 0, -1)) MOD 2 < 0, -1, TRUE, 1) * exp(sum(log(abs(iif(BOOK.ID = 0, NULL, BOOK.ID)))))) -- ASE, SQLDATAWAREHOUSE, SQLSERVER (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN (sum(CASE WHEN BOOK.ID < 0 THEN -1 END) % 2) < 0 THEN -1 ELSE 1 END * exp(sum(log(abs(nullif(BOOK.ID, 0)))))) -- AURORA_MYSQL, AURORA_POSTGRES, CUBRID, DB2, FIREBIRD, H2, HANA, HSQLDB, INGRES, MARIADB, MEMSQL, MYSQL, ORACLE, POSTGRES, -- SYBASE, VERTICA (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN MOD(sum(CASE WHEN BOOK.ID < 0 THEN -1 END), 2) < 0 THEN -1 ELSE 1 END * exp(sum(ln(abs(nullif(BOOK.ID, 0)))))) -- COCKROACHDB (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN MOD(sum(CASE WHEN BOOK.ID < 0 THEN -1 END), 2) < 0 THEN -1 ELSE 1 END * exp(sum(ln(CAST(abs(nullif(BOOK.ID, 0)) AS numeric))))) -- DERBY (CASE WHEN sum(CASE WHEN BOOK.ID = 0 THEN 1 END) > 0 THEN 0 WHEN MOD(sum(CASE WHEN BOOK.ID < 0 THEN -1 END), 2) < 0 THEN -1 ELSE 1 END * exp(sum(ln(abs(nullif(BOOK.ID, 0)))))) -- INFORMIX (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN MOD(sum(CASE WHEN BOOK.ID < 0 THEN -1 END), 2) < 0 THEN -1 ELSE 1 END * exp(sum(logn(abs(nullif(BOOK.ID, 0)))))) -- REDSHIFT (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN (sum(CASE WHEN BOOK.ID < 0 THEN -1 END) % 2) < 0 THEN -1 ELSE 1 END * exp(sum(ln(abs(nullif(BOOK.ID, 0)))))) -- TERADATA (CASE WHEN sum(CASE BOOK.ID WHEN 0 THEN 1 END) > 0 THEN 0 WHEN sum(CASE WHEN BOOK.ID < 0 THEN -1 END) MOD 2 < 0 THEN -1 ELSE 1 END * exp(sum(ln(abs(nullif(BOOK.ID, 0)))))) -- BIGQUERY, IGNITE, SNOWFLAKE, SQLITE /* UNSUPPORTED */
(These are currently generated with jOOQ 3.15, see #10141)
Feedback
Do you have any feedback about this page? We'd love to hear it!