CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nario
Nario

Reputation: 551

multiply rows in t-sql

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)

Upvotes: 17

Views: 9987

Answers (3)

sll
sll

Reputation: 62504

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

Upvotes: 26

user1054248
user1054248

Reputation: 

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

Upvotes: 2

TimothyAWiseman
TimothyAWiseman

Reputation: 14873

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.

Upvotes: 1

Related Questions