Why is there no PRODUCT aggregate function in SQL?

by

For MSSQL you can use this. It can be adopted for other platforms: it’s just maths and aggregates on logarithms.

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       Mytable
    GROUP BY
       GrpID
    ) foo

Taken from my answer here: SQL Server Query – groupwise multiplication

More Related Contents:

  1. ListAGG in SQLSERVER
  2. How to use GROUP BY to concatenate strings in MySQL?
  3. Aggregating (x,y) coordinate point clouds in PostgreSQL
  4. SQL Server : SUM() of multiple rows including where clauses
  5. Get values from first and last row per group
  6. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  7. Crosstab with a large or undefined number of categories
  8. NOT IN vs NOT EXISTS
  9. Simple way to transpose columns and rows in SQL?
  10. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  11. In MySQL queries, why use join instead of where?
  12. Most efficient way in SQL Server to get date from date+time?
  13. SQL performance on LEFT OUTER JOIN vs NOT EXISTS
  14. How to do pagination in SQL Server 2008
  15. Using Excel VBA to export data to MS Access table
  16. When to use VARCHAR and DATE/DATETIME
  17. Why are relational set-based queries better than cursors?
  18. How do I group on continuous ranges
  19. join comma delimited data column
  20. How to emulate SQLs rank functions in R?
  21. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  22. How to get second largest or third largest entry from a table [duplicate]
  23. Add business days to date in SQL without loops
  24. How to list files inside a folder with SQL Server
  25. How to create timestamp column with default value ‘now’?
  26. Autoincrement in oracle to already created table
  27. What can cause an Oracle ROWID to change?
  28. Delete all but top n from database table in SQL
  29. ORA-00933: SQL command not properly ended
  30. Difference between === null and isNull in Spark DataDrame

Leave a Comment