# Multiply vaues in one column in a group for a GROUP BY

How do you do a PRODUCT in a GROUP BY in SQL on SQL Server 7, that is multiply all the values in the group

I have a table tblTest

ID      Date      Value
1      1/1/99      2.5
2      1/1/99      2
3      1/1/99      3
4      2/1/99      4
5      2/1/99      5

I want to GROUP BY date an multipy what's in the group to return

Date      ValueProduct
1/1/99      15
2/1/99      20

i.e. for 1/1/99 the product is 2.5*2*3 =15 and for 2/1/99 it's 4*5 = 20

So I want something like

SELECT Date, PRODUCT(Value)
FROM tblTest
GROUP BY Date

But how do acheive this 'PRODUCT' Function that does not exist in SQL Server as far as I can see ?
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
One way to do this is by using cursors - create a cursor which contains all your data, sorted by date. then row by row calculate the product of you numbers, until the date changes.
when date chagnes write the result in a temp table, and then continue with the next date.

here is a sample

declare mycursor cursor for
select date,value from tblTest
go
open mycursor
declare @d varchar(100)
declare @curd varchar(100)
declare @n float
declare @product float
fetch next from mycursor into @d,@n
select @curd=@d
select @product=1
set nocount on
create table #temp (d datetime,n float)
while (@@FETCH_STATUS=0)
begin
if (@d=@curd )
begin
select @product=@product*@n
end
else
begin
insert #temp values(@d,@n)
select @curd=@d
select @product=@n
end
fetch next from mycursor into @d,@n
end
select * from #temp
drop table #temp
deallocate mycursor
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
That may be the answer.  Is there no more efficient set operation, since I need this for a huge dataset, so a cursor will be a bit slow
0
Commented:
as far as I know - no.
The problem is that you can't define yuor own aggregate function for product, and there is no such function in SQL Server.
So the only way is passing through all records.
Of course you can do this in an some kind of user progam (or extended stored procedure) - it could be faster than TSQL using cursors.
0
Author Commented:
Thanks.  Surely using a cursor would be faster than, say, a VB program using recordsets though?
0
Author Commented:
I got this answer off a SQL Server Newsgroup which you may find interesting.  Looks very smart:

And to add to BP and Umachandar's cool solution,
If thereARE negative values, you can

1) ignore them by using ABS
EXP(SUM(LOG(ABS(Value))))

Or if you really need to keep track of the sign of the result,

-- The next line returns a -1 for odd number of negative values, and +1
otherwise.....
(Case (Sum(Case When Value <0.0 Then 1 Else 0 End) % 2) When 1 Then -1 Else
1 End)
*
EXP(SUM(LOG(ABS(Value))))

This is somewhat complex, but I can't think of any simpler way to handle
negatives...

Regards,

Charly

BPMargolin <bpmargo@ibm.net> wrote in message
news:O5baWfuI\$GA.283@cppssbbsa04...
> Tom,
>
> I love the answer to this one, because it's elegant even though I would
> never of thought it up myself:
>
> PRODUCT = EXP(SUM(LOG(Value)))
>
> There are all the usually proviso's that LOG can only accept values that
are
> greater than zero.
0
Commented:
It's hilarious that this post is still useful today.  Tom Nich if you're still out there, you rock!  Thanks for posting this.

PRODUCT = EXP(SUM(LOG(Value)))

Love it.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.