Menu
Im looking for something like SELECT PRODUCT(table.price) FROM table GROUP BY table.sale similar to how SUM works.
Have I missed something on the documentation, or is there really no PRODUCT function?
If so, why not?
Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql does not support it.
803
SQL> with yourTable as 2 ( select 1 yourColumn from dual union all 3 select 2 from dual union all 4 select 4 from dual union all 5 select 8 from dual 6 ) 7 select EXP(SUM(LN(yourColumn))) As ColumnProduct from yourTable 8 / COLUMNPRODUCT ------------- 64 1 row selected.
You cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its SELECT list often includes a WHERE clause that restricts the rows to which the aggregate is applied.
Which of the following is not a built in aggregate function in SQL? Explanation: SQL does not include total as a built in aggregate function. The avg is used to find average, max is used to find the maximum and the count is used to count the number of values.
All you need to do is use the multiplication operator (*) between the two multiplicand columns ( price * quantity ) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price .
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
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
