Multiplying values from different rows

[Pettor]

Hello all,


I am now learning SQL and I have one question. I would like to retrieve the last row's data from the table above it.
Meaning that I would like to group the data based on the number 10248, receiving the product of the three numbers of the table rows (11*42*72).
Probably for someone else this should be super easy but I don't know the methodology to do something like that.
Every help is much appreciated. Thanks!


A B C


1 10248 11
2 10248 42
3 10248 72




10248 3 33264

 

[Joe4]

Wow, that is a tough one.
Access (and SQL) have Aggregate functions, but Product is not one of them (you can Sum, Average, or Count the numbers, but not Multiply them).
See: https://support.office.com/en-us/ar...unctions-a810ee71-c1cd-43d7-9c55-8005f1893be5

If it was me, I would consider one of the following methods:
- Use VBA to loop through the Recordset, do the calculations, and write the results to a new table
- Export to Excel and try doing it there

 

[63falcondude]

If you do end up doing this with Excel, you can try something like this:

Excel 2010
	A	B	C	D	E	F
1	10248	11
2	10248	42		10248	3	33264
3	10248	72
4	10212	36
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIF(A:A,D2)
F2	{=PRODUCT(IF(A1:A4=D2,B1:B4))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marbles]

If it was always three numbers, you could group by B, get the Sum, Min and Max of C.
Then a field with Sum-(Min+Max) to get the third number.
Then a field with Min*Max*the above calculation.
But I'm afraid it would only work with three grouped.

 

[james_lankford]

ooh, ooh

I think I know how to do the multiplication

I had a similar question in a job interview years ago

10 ^ ( sum ( log (field_name) ) )

https://people.richland.edu/james/lecture/m116/logs/properties.html
The log of a product is the sum of the logs.
log_a xy = log_a x + log_a y


didn't get the job though

 

[Pettor]

Hi All,

Thank you for your help. The last suggestion worked (exp(sum(log()))!!

 

[63falcondude]

That's really cool. Multiplication through logs and addition.

Pettor, wouldn't it be 10^(sum(log())) and not e^(sum(log())) ?

 

[Pettor]

I don't know about 10^ but exp worked pretty well.

 

[63falcondude]

I don't know about 10^ but exp worked pretty well.

Are you sure that you are getting the correct answer? exp(x) is the same as e^x and e is roughly = 2.72.

Take a simple example of 2 and 3 (we know that the product should be 6).

log(2)+log(3) ≈ 0.778

10^0.778 = 5.998 ≈ 6 whereas e^0.778 ≈ 2.178 ≠ 6

The only way that using e (or exp) should work would be if you took the natural logarithm (log base e a.k.a. "ln") of the numbers and not the log (base 10) of the numbers.

i.e. you can either do this one of two ways:

1) 10^(log(2)+log(3))

or

2) exp(ln(2)+ln(3))

 

[xenou]

vba log function (used in MSAccess, and unlike the Excel Log() function) returns natural log so that's why it works.

vba: exp((log(11)+log(42)+log(72)))
excel: 10^(log(11)+log(42)+log(72))
excel: exp(ln(11)+ln(42)+ln(72))

all give answer the same answer of 33264

didn't get the job though

yikes