Need some help

[jerry]

Hi,Jerry on this side

Just wanted to ask if someone could help me,
My problem is this
I have 1 table looks like this:

Field Field1 Field2
001 2.3 ab
002 2.4 abc
003 5.1 acc
....
....
....
What I need now is the product of field 1
(2.3*2.4*5.1)
I will use it in a form
Can anyone please help me further?

 

[Sylvain Lafontaine]

Read the thread "Multiply record fields with same mainID " from the
microsoft.public.sqlserver.server newsgroup on 4/21/2005.

Here the final solution from David Portas:

SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid

Taken from an idea from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-SQLBanker.asp