SQL Server Puzzle #3: aggregate product

[vongrunt]

Prob... puzzle #3.

Short description
Write query that calculates product per group.

Long description (sample data included)

Product of all values in a group = something like SUM but with * instead of +. Lemme illustrate that with sample data:

create table myTable
(	groupID varchar(16),
	numValue int
)

insert into myTable values ( 'Group 1', 1 )
insert into myTable values ( 'Group 1', 2 )
insert into myTable values ( 'Group 1', 3 )
insert into myTable values ( 'Group 2', 1 )
insert into myTable values ( 'Group 2', -5 )
insert into myTable values ( 'Group 2', 2 )
insert into myTable values ( 'Group 3', 4 )
insert into myTable values ( 'Group 3', 0 )
insert into myTable values ( 'Group 3', 1 )
insert into myTable values ( 'Group 4', -2 )
insert into myTable values ( 'Group 4', -4 )
insert into myTable values ( 'Group 5', 7 )
insert into myTable values ( 'Group 5', NULL )
insert into myTable values ( 'Group 6', NULL )

Expected results are:

groupID  groupProduct
-------.-------------
Group1              6
Group2            -10
Group3              0
Group4              8
Group5              7
Group6            NULL

Rules & restrictions

- code must work well for ALL numeric values and NULL
- Everything must be done with a single query
- only SQL Server 2000 features are allowed

IMHO this puzzle is too simple to wait for next Friday... shoot immediately.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Well? No slam-dunks, heh?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

now i understand what mistakes i made

-DNG

 

[SQLDenis]

mmmmm

getting this instead

Group 1 6
Group 2 -10
Group 3 0
Group 4 8
Group 5 NULL
Group 6 NULL

Group 5 needs to be 7


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Ok so I cheated......or not.....
select case when max(t2.numValue) is null then max(1 * t1.numValue )
else max((coalesce(t1.numValue,1) * (t2.numValue))) end,t1.groupID
from mytable t1 left join mytable t2 on t1.groupID =t2.groupID
and coalesce(t1.numValue,0) <> t2.numValue
and t1.numValue <> 1
and t2.numValue <> 1
group by t1.groupID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> Ok so I cheated......or not.....

Not - because 1*anything = anything.

Still, I'd like to see that without a) self-joins, b) conditional statements.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

And btw. if some rows are duplicated (insert 3rd sample row for example), results will be wrong because of COALESCE() condition in outer join.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

I know that's why I said I cheated

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

More cheating.....

select groupID,numValue =min(coalesce(numValue,1)) *max(numValue)
from mytable
where (coalesce(numValue,0) <> 1 )
group by groupID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[johnv20]

I know its a bit long but since Log(A*B) = Log(A) + Log(B)
here's another way to do it

CREATE  FUNCTION dbo.jvmult
 (@groupID varchar(8))  
RETURNS int
 AS  
BEGIN

declare @mult int,
	@t int,
	@i int

-- if we have a 0 then go straight to the end
if exists ( select numValue from myTable where groupID = @groupID and numValue = 0)
	begin
	select @t = 0
	goto ender
	end

select @mult = 1

-- find out how many -ve numbers we have
select @i =
	(
	select count(numValue)
	from myTable
	where numValue < 0
	and groupID = @groupID
	)
-- if our final result is -ve then set multiplication factor to -1
if (@i%2 = 1)
	begin
	select @mult = -1
	end


-- do the math stuff
select @t = (select round(exp(sum(log(abs(numValue)))), 0) from myTable where groupID = @groupID
		and numValue != 0)

select @t = @t*@mult

ender:
return @t

end

all you need to do then is

select distinct groupID, dbo.jvmult(groupID)
from myTable

 

[vongrunt]

Works, all ingredients are here - LOG/EXP, avoided domain errors, 0 if any value is 0, otherwise -1 if count of negative values is odd etc - but... UDF is written specifically for that purpose (hardcoded myTable/groupID) and code cannot be qualified as "single query". Worth a star anyway... later.

Any takers?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)