SQL Query to find the product of values in same column

Question

Interview Question asked before.

Given a table with columns boxName and value,find the volume of each box. Value field has the lenght,bredth and height of a cube. I am required to multiply all the tree dimensions.

I can use a groupby if the sum need to be calculated, but here product is required

box1 = 12*13*1  = 156
box2 = 1*23*6   = 138
box3 = 12*1*20  = 240

+---------+-------+
| boxName | value |
+---------+-------+
| box1    |    12 |
| box1    |    13 |
| box1    |     3 |
| box2    |     1 |
| box2    |    23 |
| box2    |     6 |
| box3    |    12 |
| box3    |     1 |
| box4    |    30 |
+---------+-------+

score 2 · Accepted Answer · edited May 23 '17 at 11:44

Try this

select EXP(SUM(LN(value))) As Product_val ,boxName
from yourTable
Group by boxName

Note : value <= 0 LOG will fail.

When you have value <= 0 then use this.

SELECT
    boxName,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       boxName, 
       --log of +ve row values
       SUM(LN(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
       yourTable
    GROUP BY
       boxName
    ) foo

Refered from this answer

The whole story and an alternative using xml in the comments: https://mwidlake.wordpress.com/2012/02/22/sql-quiz-how-to-multiply-across-rows/ — Eddy, Dec 24 '15 at 16:24

score 1 · Answer 2 · answered Dec 24 '15 at 16:30

If you know for a fact that each box will have exactly 3 rows for the 3 dimensions, you can use the row_number() analytic function to uniquely identify the 3 dimensions, and then use max(case ...) to extract the 3 dimensions and multiply them:

select boxName,
       max(case when rn = 1 then value end) *
       max(case when rn = 2 then value end) *
       max(case when rn = 3 then value end) as volume
  from (select t.*,
               row_number() over (partition by t.boxName order by null) as rn
          from yourTable t)
 group by boxName

SQL Query to find the product of values in same column

2 Answers2