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
tomnichAsked:
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.

tchalkovCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
tomnichAuthor 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
tchalkovCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tomnichAuthor Commented:
Thanks.  Surely using a cursor would be faster than, say, a VB program using recordsets though?
0
tomnichAuthor 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
MatMellonCommented:
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.