How to Multiply All Values Within a Column with SQL Like Sum()

How to Multiply all values within a column with SQL like SUM()

Using a combination of ROUND, EXP, SUM and LOG

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!3/d43c8/2/0

Explanation

LOG returns the logarithm of col a ex. LOG([Col A]) which returns

0
0.6931471805599453
1.0986122886681098
1.3862943611198906

Then you use SUM to Add them all together SUM(LOG([Col A])) which returns

3.1780538303479453

Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453']))) which returns

23.999999999999993

Then this is finally rounded using ROUND ROUND(EXP(SUM(LOG('23.999999999999993'))),1) to get 24

---

Extra Answers

Simple resolution to:

An invalid floating point operation occurred.

When you have a 0 in your data

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
WHERE [Col A] != 0

If you only have 0 Then the above would give a result of NULL.

When you have negative numbers in your data set.

SELECT (ROUND(exp(SUM(log(CASE WHEN[Col A]<0 THEN [Col A]*-1 ELSE [Col A] END))),1)) * 
(CASE (SUM(CASE WHEN [Col A] < 0 THEN 1 ELSE 0 END) %2) WHEN 1 THEN -1 WHEN 0 THEN 1 END) AS [Col A Multi]
FROM yourtable

Example Input:

1
2
3
-4

Output:

Col A Multi
-24

SQL Fiddle: http://sqlfiddle.com/#!3/01ddc/3/0

How to multiply all values in one column to make one number?

In absence of an aggregate "product" function in SQL, one method uses arithmetic: you can sum the logarithm of each value, then take the the exponential of the result.

select exp(sum(ln(price_rate))) as price_factor
from mytable

FOr this to work properly, all values of price_rate must be greater than 0.

Aggregate function to multiply all values

Yes, there's a technique described here in this blog:

Basically you take the natural log of SUM and then do an exponential (e^x)

SELECT EXP (SUM (LN (col))) as product from t;

Since the output of this would be a floating point, you may do a FLOOR

FLOOR( EXP (SUM (LN (col))) ) 

DEMO

Note: I've just found that this would fail if one of the rows has 0. so you should use a separate condition or a with clause that if one of them is zero the product should be zero.

MySQL - Use GROUP BY and SUM to multiply two columns

As noted by patrick3853 and Olivier Depriester in the comments, the line SUM(jo.quantity * jo.price) should instead be SUM(jo.quantity) * SUM(jo.price).

The correct query is as follows:

SELECT p.name AS 'Project Name',
       SUM(jo.quantity) AS 'Job Order Quantity',
       SUM(jo.price) AS 'Job Order Price',
       SUM(jo.quantity) * SUM(jo.price) AS 'Cost'
FROM projects p
JOIN job_orders jo ON p.id = jo.project_id
GROUP BY  name;

How do I multiply and sum all values in two SQL tables

If the tables are connected by a column that can be used to join, the query below should work.

select sum(t1.col2 * t2.col2)
from t1 join t2 on t1.col1 = t2.col1

Fiddle with sample data

Fiddle with the data from question

Multiply 2 columns in sql and to sum all the results using SQL

Original Answer

select Sum(Rate) as Rate, Sum(Qty) as Qty, Sum(Rate*Qty) as Result from tblName

EDIT - Try this..

select 
     0 as TotalRow, 
     Rate,
     Qty,
    (Rate*Qty) as Result 
from tblName

UNION ALL

select 
     1 as TotalRow,
     Sum(Rate) as Rate, 
     Sum(Qty) as Qty, 
     Sum(Rate*Qty) as Result 
 from tblName

Order By TotalRow

multiply and SUM() MS SQL

@fia Maybe you should do your calculation elsewhere first for example on paper or in Excel to ensure you know exactly what you should get. It would also help you figure out the order of the calculation before writing it in SQL. According to the figures shown the values you've stated seems to be correct i.e 22,720 for multiply then sum the figures as is, or 5,920 if you use the Absolute value of the second column. If both columns use absolute values then it will also give 22,720. If you're sure you need to get 14,320 then you may need to get clarification of the calculation needed or revise that number.

Additionally, your values may be wrong. You can get 14, 320 if the values for the 2nd and 3rd rows in the second column was -1 and 1 respectively.

---

Related Topics