Help !!! Product of a Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help !!! Product of a Column

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2004-09-09 : 02:34:57

Hello ppl,
I been asked to write a script which gets the product of all values in a column(small int) of a medium sized table, which is easy but the challenging part is the column contains both -ve values and zero. I need only the product of +ve data and ofcourse omitting zero values.

Is there any to do this without using loop or cursor?

Many Thanks
cindy

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-09 : 03:18:19

Bit of clarification needed here methinks. Do you mean that you want to ignore negatives completely -

SELECT SUM (case when field > 0 then field else 0 end) as sum_of_values from table

or treat them as if they were positive?

SELECT SUM (abs(field)) as sum_of_value from table

-------
Moo. :)

cindylee
Yak Posting Veteran

55 Posts

Posted - 2004-09-09 : 03:30:44

Yes, ignore -ve values completely.
Also i want the product(multiply) not SUM

Thanks
cindy

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-09 : 04:15:51

I'm not sure what you want to multiply the values by?

Can you give an example set of data showing what results you would expect?

-------
Moo. :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 04:16:08

[code]set nocount on
declare @mytable table (col smallint)
insert @mytable(col)
select 6 union select -7 union select 7 union select null union select 2

declare @x bigint
set @x = 1
select @x = @x * col from @mytable where coalesce(col,0) > 0

select col from @mytable
select @x as positive_product[/code]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

cindylee
Yak Posting Veteran

55 Posts

Posted - 2004-09-09 : 07:33:47

Sample Data:

dup_column
----------
2
4
6
0
-1

result:
48

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-09 : 07:36:54

rockmoose's solution should do it.

-------
Moo. :)

Tracey
Starting Member

40 Posts

Posted - 2004-09-09 : 08:03:52

Maybe try this:

SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG( ABS(NULLIF(dup_column,0))))),0),0) AS INTEGER) AS output_value FROM MyTable

tracey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-09 : 08:20:45

tracey: just a slight modification to what cindy needs:
where col > 0
because that will product all values including negative.

but this is a great solution IMHO. if you don't count all the operations.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-09 : 08:30:47

Thank You Tracey, that was very neat

For readability, and to get rid of the nullif, abs... :

select exp( sum( log(dup_column) ) )
from mytable
where coalesce(col,0) > 0

Then cast and round to your liking!

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Kristen
Test

22859 Posts

Posted - 2004-09-09 : 23:11:33

"CAST(ROUND(COALESCE(EXP(SUM(LOG( ABS(NULLIF(dup_column,0))))),0),0) AS INTEGER)"

Fantastic! I'm itching to see if I can squeeze some more nested functions in!

But I think I'll set it as an "What does this do" interview questions!

Kristen

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:22:01

if you dont know any function then you are lost in interview.

mk_garg

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 04:36:02

acctualy this is a simple math problem: sum of logs is a product when exp-ed

this would be ideal for a question, because even if u don't know functions, the
interviewer sees how u handle unknown situations (handle a problem) plus your knowlegde of math :))

i'd ask this this first:
exp(sum(log(Col1)))

then if he knew the answer add a little more nested goodies in

Go with the flow & have fun! Else fight the flow

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-09-10 : 07:19:00

Just to give a precise answer here:

SELECT EXP(SUM(LOG(dup_column))) WHERE dup_column > 0

This will give you the desired result. TOTALLY confusing that stuff Tracy pulled...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources