sql server - multiply values by themselves

Question

there is a way for aggregate values in multiplication (*) operator rather than + Operator?. for this example the result will be 140 (4*5*7=140)

SELECT  SUM(V) --*?
FROM    ( SELECT    4 V
          UNION ALL
          SELECT    5
          UNION ALL
          SELECT    7
        ) Q

Nothing built in. You'll probably need to use a cursor and do the calculation yourself. Otherwise do it on the client. But also note range of ints on SQL Server: you'll not need many rows before overflowing (compound multiplication of even small values greater than one gets big quickly). — Richard, Jan 19 '17 at 10:55

score 1 · Accepted Answer · answered Jan 19 '17 at 10:58

       select *
    into #a --*?
    FROM    ( SELECT    4 V
              UNION ALL
              SELECT    5
              UNION ALL
              SELECT    7
            ) Q

 select EXP(SUM(LOG(v))) As value from #a

or

SELECT  EXP(SUM(LOG(v)))
FROM    ( SELECT    4 V
          UNION ALL
          SELECT    5
          UNION ALL
          SELECT    7
        ) Q

sql server - multiply values by themselves

1 Answers1