multiply rows in t-sql

Question

I have following table

 ID     Number    
----------------     
   1       41.5
   2       42.5
   3       43.5
   2       44.5   
   2       45.5
   1       46.5
   1       47.5

I need to write a query which will return distinct ID's and corresponding Number column values multiplied. For the given table result should be like this

ID       Result 
-----------------
  1         41.5 * 46.5 * 47.5
  2         42.5 * 44.5 * 45.5
  3         etc...

(without use cursors)

sll · Accepted Answer · 2012-12-28T14:38:09.327

26

SELECT Id, EXP(SUM(LOG(Number))) as Result
FROM Scores 
GROUP BY id

This will work for positive numbers, to multiply negative numbers as well you can use ABS() function to use absolute (positive) value but final result will be positive rather than negative number:

SELECT Id, EXP(SUM(LOG(ABS(Number)))) as Result
FROM Scores 
GROUP BY id

EDIT: Added test script

DECLARE @data TABLE(id int, number float)

INSERT INTO @data VALUES
(1, 2.2),
(1, 10),
(2, -5.5),
(2, 10)

SELECT Id, EXP(SUM(LOG(ABS(Number)))) as Result 
FROM @data GROUP BY id

Output:

1   22
2   55

edited Dec 28 '12 at 14:38

answered Nov 18 '11 at 16:53

sll

61,540
22
104
156

I saw that too, but I'm a bit dubious about involving complicated calculations like `EXP` and `LOG` in what should be simple arithmetic. Also doesn't work for negative numbers, but the first comment here has a workaround: http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspx – mellamokb Nov 18 '11 at 16:54
2

EXP is not aggregate function – Nario Nov 18 '11 at 16:58
@mellamokb : this would be interesting to compare execution plans – sll Nov 18 '11 at 16:58
2

@Nario : I've added the test script so you can try out it to see whether EXP woudl work with GROUP BY – sll Nov 18 '11 at 17:07
2

@Nario EXP is not an aggregate function, but here it is called after sum which is. So in the end it is only operating on one number per row and it works quite well that way. One way to think of it is that the Sum wraps the aggregated column before handing it off to Exp. – TimothyAWiseman Nov 19 '11 at 00:57
have to think about zero and negative values http://stackoverflow.com/questions/3653586/sql-server-query-to-bring-groupwise-multiplication – gbn Nov 22 '11 at 07:24
@gbn : negative numbers case is covered by `ABS()`, something I missed? – sll Nov 22 '11 at 10:10
@gbn: right, I've noted this point in answer `but final result would be positive rather than negative numbe`. It would be great to add solution which persist sign – sll Nov 28 '11 at 21:55

score 2 · Answer 2 · edited Nov 18 '11 at 17:52

2

select id, power(sum(log10(num)),10) group by id

edited Nov 18 '11 at 17:52

animuson

53,861
28
137
147

answered Nov 18 '11 at 17:01

TimothyAWiseman · Answer 3 · 2011-11-18T17:12:11.920

This is a slight variation on row concatenation and Jeff Moden has an excellent article on that at SQL Server Central titled Performance Tuning: Concatenation Functions and Some Tuning Myths

Edit: @mellamokb It is analogous to concatenation, but requires some modification. A sample script would be

create table testMult (id int, num int)

GO

insert into testMult values (1, 2)
insert into testMult values (1, 3)
insert into testMult values (1, 4)
insert into testMult values (2, 2)

GO

create function dbo.fnMult (@someId int)
returns int as 
begin
    declare @return int
    set @return = 1

    select @return = @return * num
    from testMult
    where id = @someId

    return @return
end

GO

select * 
from testMult

select t1.id,
    dbo.fnMult(t1.id) 
from testMult t1
group by t1.id

Which is just a very small variation on the script provided by Jeff Moden in his article.

You misread his question the same way I did the first time :) It's multiplication, not concatenation. — mellamokb, Nov 18 '11 at 16:52

multiply rows in t-sql

3 Answers3

Linked