April 25, 2012 at 3:06 am
Hi,
All
Need Help
input:
declare @t table
(
val numeric(18,6),
rownumber numeric(18,6)
)
insert into @t
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4
OutPut:
1(1)
2(1*2)
6(1*2*3)
24(1*2*3*4)
Please help me
April 25, 2012 at 4:09 am
This is how I managed it.
--Original Table that holds the data
Create table Ex
(
val numeric(18,6),
rownumber numeric(18,6)
)
--Inserting Data
insert into Ex
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4
--Selecting Geometric Mean
Declare @t Table(val numeric(18,6), rownumber numeric(18,6) )----temp table for looping
Declare @t1 Table(gm numeric(18,6) )----temp table for storing result
Declare
@temp1 numeric(18,6),
@temp2 int,
@loop int = 1,
@res numeric(18,6) = 1
Insert Into @t
Select * From Ex
Select @temp2 = COUNT(*) From Ex
While(@loop <= @temp2)
Begin
Select Top 1 @temp1 = val From @t
Set @res = @res * @temp1
Insert Into @t1 Values(@res)
Delete From @t Where val = @temp1
Set @loop = @loop + 1
End
--Viewing the result
Select * From @t1
But, the pros in this forum might have a better solution. 🙂
April 25, 2012 at 4:20 am
Hi,
Thanks for your reply.
I want this Logic without using the Looping concept.
Thanks.
April 25, 2012 at 7:26 am
Hi, is there any specific reason why you wouldnt want to use loop for this?
that seems to be the simplest solution, also, I am assuming that the number of rows and the data in table @t are not static and they can change right?
April 25, 2012 at 7:29 am
phani.gudmines (4/25/2012)
.....1(1)
2(1*2)
6(1*2*3)
24(1*2*3*4)
what you have as your requested out is the factorial of the number. do you really want the geometric mean of the 4 entries (take the 4th root of the numbers multiplied together) or the factorial as above.
if you want the factorial as what you have posted i can do it if you actually want the geometric mean that is nice and easy
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 7:34 am
AllaboutSQL (4/25/2012)
Hi, is there any specific reason why you wouldnt want to use loop for this?that seems to be the simplest solution, also, I am assuming that the number of rows and the data in table @t are not static and they can change right?
you can use a recursive function or cte and not "Loop" (not a while loop but you are still looping) but it is starting to sound like homework so ill leave it at that
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 8:22 am
or do you want a product function where if you have the values 1,3,5,7 the result would be 1*3*5*7 and you just happened to use 1234 so it looks like a factorial. once again not the geometric mean but extremely do able with out a loop.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 8:54 am
I know I am doing someone else's homework but I havent worked much on CTE or recursive CTEs and it is like a learning exercise for me. When you say recursive CTE, are you guys talking about something like this -
with cte1 (val, newval) as
(
select 1,1 union all select val+1, (val+1)*newval from cte1 where val <=4
)
select * from cte1
with cte2 (val, newval) as
(
select 1,1 union all select val+2, (val+2)*newval from cte2 where val <=7
)
select * from cte2
April 25, 2012 at 9:05 am
yep those are recursive cte's. im currently trying to solve the factorial with a numbers table and i think im close. should have it soon
EDIT: can only do one value at a time but the factorial is as follows
With tally (n) AS (SELECT top 100 ROW_NUMBER OVER (ORDER BY (SELECT NULL)) FROM sys.columns)
DECLARE @fac INT = 1
SELECT @fac = @fac * n FROM tally where n <= 6
SELECT @fac
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 9:21 am
This looks like a running total problem to me (with multiplications as opposed to additions). Most set-like solutions are going to run into some pretty horrendous triangular joins quite quickly. You could look at Jeff Moden's "Quirky Update" method, but since this particular problem must have quite a small number of rows (as the numbers quickly get very silly), a loop/cursor based solution isn't all that bad...
April 25, 2012 at 9:35 am
HowardW (4/25/2012)
This looks like a running total problem to me (with multiplications as opposed to additions). Most set-like solutions are going to run into some pretty horrendous triangular joins quite quickly. You could look at Jeff Moden's "Quirky Update" method, but since this particular problem must have quite a small number of rows (as the numbers quickly get very silly), a loop/cursor based solution isn't all that bad...
if the OP wants to just multiply all the numbers with out the running total he can use SELECT EXP(SUM(LOG(NumCol))) FROM table[/code] to get the final result. so it depends on what the OP wants. and as stated before this is still not the geometric mean for the records. geometric mean would be SELECT EXP(AVG(LOG(NumCol))) FROM table
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 9:48 am
This is what you are trying to do -
DECLARE @fac INT ;
set @fac = 1;
With tally (n) AS (SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns)
SELECT @fac = @fac * n FROM tally where n <= 6
SELECT @fac
and this results into a value of 720.
What is the difference between doing what you have done and doing the following which I have mentioned in my earlier post -
with cte1 (val, newval) as
(
select 1,1 union all select val+1, (val+1)*newval from cte1 where val <=4
)
select * from cte1
April 25, 2012 at 10:01 am
capn.hector (4/25/2012)
if the OP wants to just multiply all the numbers with out the running total he can useSELECT EXP(SUM(LOG(NumCol))) FROM tableto get the final result.
It's easy enough to expand that to a running total using a triangular join, but performance is not very scalable:
WITH n AS (
SELECT 1 n
UNION ALL
SELECT 2 n
UNION ALL
SELECT 3 n
UNION ALL
SELECT 4 n
UNION ALL
SELECT 5 n
UNION ALL
SELECT 6 n
UNION ALL
SELECT 7 n
)
SELECT n.n,EXP(SUM(LOG(n1.n))) FROM n
INNER JOIN n n1 ON n1.n<=n.n
GROUP BY n.n
April 25, 2012 at 11:08 am
AllaboutSQL (4/25/2012)
This is what you are trying to do -DECLARE @fac INT ;
set @fac = 1;
With tally (n) AS (SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.columns)
SELECT @fac = @fac * n FROM tally where n <= 6
SELECT @fac
and this results into a value of 720.
What is the difference between doing what you have done and doing the following which I have mentioned in my earlier post -
with cte1 (val, newval) as
(
select 1,1 union all select val+1, (val+1)*newval from cte1 where val <=4
)
select * from cte1
mine takes a number from tally and multiplies @fac by it setting @fac to the result where as the recursive cte calculates each new number for the next multiplication. different way to get the same result and over large numbers of values recursive CTE's can be a performance drain. for a factorial the largest we can get with a NUMERIC(38,0) is 33 so the difference over the small number of rows is un noticible. if we add to 32,000 the tally table would be faster.
DECLARE @fac INT ;
print '---===TallyTable adding===----'
SET STATISTICS TIME ON
set @fac = 1;
SELECT @fac = @fac + n FROM tally where N <= 32000
SET STATISTICS TIME OFF
PRINT '----====RECURSIVE CTE adding ===== ------'
SET STATISTICS TIME ON;
with cte1 (val) as
(
select 1 union all select val + 1 from cte1 where val <= 32000
)
select @fac = val from cte1 OPTION(MAXRECURSION 32000)
SET STATISTICS TIME OFF
print '---===SELECT ROW_NUMBER "adding"===----'
SET STATISTICS TIME ON
SELECT top 32000 @fac = ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a, sys.all_columns b
SET STATISTICS TIME OFF
the results
---===TallyTable adding===----
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 7 ms.
----====RECURSIVE CTE adding ===== ------
SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 502 ms.
---===SELECT ROW_NUMBER "adding"===----
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply