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, EXASOL, IGNITE, SNOWFLAKE, SQLITE
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.15, see #10141)

The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : PRODUCT

previous : next