Solved

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

Posted on 1999-10-30
Medium Priority
2,405 Views
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 ?
0
Question by:tomnich
• 3
• 2

LVL 7

Accepted Solution

tchalkov earned 800 total points
ID: 2171357
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

LVL 1

Author Comment

ID: 2171369
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

LVL 7

Expert Comment

ID: 2171374
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

LVL 1

Author Comment

ID: 2171712
Thanks.  Surely using a cursor would be faster than, say, a VB program using recordsets though?
0

LVL 1

Author Comment

ID: 2171719
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

Expert Comment

ID: 39235321
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll