How to find the Product of 4 Numbers in a Column?

Hi I have a table with this data

Description Number

-
Something1 2
Something2 3
Something3 4
Something4 6

I would like to find the product of 4 numbers (2*3*4*6) as output (144)

Please advice

Thanks

hi try this

SELECT *
INTO #Number
FROM (
SELECT 1 as Something,2 as Numbers UNION ALL
SELECT 1 as Something,3 as Numbers UNION ALL
SELECT 1 as Something,4 as Numbers UNION ALL
SELECT 1 as Something,6
) Number

DECLARE @.Product int

SET @.Product = 1

SELECT @.Product = @.Product * Numbers
FROM #Number

SELECT @.Product

DROP TABLE #Number|||

There was a request here to find a generic method of finding aggregate products of column entries:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1435635&SiteID=1

Umachandar posted a reference to a site here that has a generic solution that can be used:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

|||

Nice answer

But I am expecting answer without using those values in the select statement

Some times I have only 3 rows sometimes I have 6 or 7 rows

But I want to find the product of those numbers in a Column

Please advice

Thanks

|||hi, those were only sample data to test if the query works. you can then filter the values from the other columns.

umandars' aggregate product were great! two thumbs up umandar..|||

Here's little way to do what you ask.

Create a string that is the formula. Evaluate the string:

Code Snippet

declare @.Formula varchar(8000)

select @.Formula = isnull(@.Formula + ' * ' + convert(varchar,[Number]),convert(varchar,[Number])) from <DataTable>

select @.Formula

exec ('select ' + @.Formula)

|||

Simply superb

I got my answer from your query

Thanks a lot

With Regards

how to find a simple quote in a row

Hi,
I have a table with a "description" row.
In this row I may have some simple quote (') character.
How can I select them ?
something like :
select * from myTable where description like %'%
thanksselect * from myTable where description like %''%

Use TWO single quotes. They are treated as one.|||don't forget the quotes around the pattern :)

... where description like '%''%'|||Other alternative is

select * from tableName where charindex('''',Colname)>0