Multiply Row Values

[vlitim]

I have a table that has a column cost, what I need to do is multiply together all values in that columns, ie:

cost
1
5
10

and get 50

can't be that difficult but i can't do it!
Cheers
Tim

 

[donutman]

[Blue]DECLARE[/Blue] @F [Blue]AS[/Blue] [Blue]float[/Blue]
[Blue]SET[/Blue] @F[Gray]=[/Gray]1.
[Blue]SELECT[/Blue] @F[Gray]=[/Gray]@F[Gray]*[/Gray]Cost [Blue]FROM[/Blue] YourTable
[Blue]SELECT[/Blue] @F

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vlitim]

cool, what happens then if I added another column to the table:

cost area
1 1
5 1
10 2

so that you would get two results on for each area!


cheers

 

[vongrunt]

You want 5*1 for area 1 and 10 for area 2?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Vongrunt, save me here. I don't want to write a UDF for this. I think he wants a group by area of cost.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vlitim]

yeah, ideally returned in a table

cost area
5 1
10 2

 

[vongrunt]

Nah, I'm not evil that much :X

Idea: if log(A*B*C) = log(A) + log(B) + log(C) then:

select area, exp(sum(log(cost)))
from myTable
group by area

You fix cases for cost <=0... I have this deja-vu feeling (ESquared?)

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Hehe, I knew ESquared had solved it too, but I couldn't remember how. Actually, I was thinking of a different thread where he showed how to do the same grouping with a concatenating string. I can't find that one.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vlitim]

that looks great, cheers I will have a look at it later....

 

[sunila7]

Another solution... Same Idea though....

select area, power(10.0, sum(log10(cost)))
from TBL
group by area






Sunil