By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

multiply each records in field

P: 2
How can we write a query so that all the value in the ‘test’ field will be multiplied to get 1 value at the end. Meaning that 100% * 96.13% * 99.71% * 100% *…* 100% where at the end it will give answers of 3.01% when all the values is been multiplied.

The results of the query is base on the process. The below records is based on process of 'SR113"


Process QtyIn QtyOut SumIn SumOut test
SR113 38848 38848 38848 38848 100.00%
SR113 38848 37343 38848 37343 96.13%
SR113 37343 37236 37343 37236 99.71%
SR113 38848 38848 38848 38848 100.00%
SR113 39630 37467 39630 37467 94.54%
SR113 37467 4150 37467 4150 11.08%
SR113 4144 3630 4144 3630 87.60%
SR113 3516 1279 9716 3577 36.38%
SR113 1239 1239 3537 3537 100.00%
SR113 1238 1164 3536 3306 94.02%
SR113 1158 1158 3300 3300 100.00%
May 17 '07 #1
Share this Question
Share on Google+
3 Replies


Expert
P: 97
You need the PRODUCT() function. This unfortunately does not exist in SQL.
You could write a function that would open the record set and parse every record to return the product.
Or you could use the method used before the age of calculators - summing logarithms!

POWER(10.0, SUM(LOG10([test])))

Microsoft:How to calculate the product of a field

Note that you will have to avoid zeroes and nulls and negative numbers, but from your sample data that shouldn't be a problem.
May 17 '07 #2

ADezii
Expert 5K+
P: 8,627
You need the PRODUCT() function. This unfortunately does not exist in SQL.
You could write a function that would open the record set and parse every record to return the product.
Or you could use the method used before the age of calculators - summing logarithms!

POWER(10.0, SUM(LOG10([test])))

Microsoft:How to calculate the product of a field

Note that you will have to avoid zeroes and nulls and negative numbers, but from your sample data that shouldn't be a problem.
Assuming these Fields [SumIn] and [SumOut] exist in a Table named tblProcess, the following SQL Statement will generate the correct result as an Overall Percentage:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblProcess.SumIn) AS Total_Sum_In, Sum(tblProcess.SumOut) AS Total_Sum_Out, Format(Sum(tblProcess.SumOut)/Sum(tblProcess.SumIn), "Percent") AS Total_Percentage
  2. FROM tblProcess;
May 17 '07 #3

Expert
P: 97
You need the PRODUCT() function. This unfortunately does not exist in SQL.
You could write a function that would open the record set and parse every record to return the product.
Or you could use the method used before the age of calculators - summing logarithms!

POWER(10.0, SUM(LOG10([test])))

Microsoft:How to calculate the product of a field

Note that you will have to avoid zeroes and nulls and negative numbers, but from your sample data that shouldn't be a problem.
My apologies, the POWER and LOG10 functions only exist in MSSQL Server
In Access use:
Product_test: Exp(Sum(Log([test])))
I have tested this with your data and produced the result 0.0300843106589849 as expected.
May 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.