[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
Medium Priority
906 Views
Similar to GEOMEAN in excel, I am try ing to get the Mean using SQL2000 and SSRS.  Could somebody help me with this?
0
Question by:epicazo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 26

Expert Comment

ID: 34920686
this will calculate the geometric mean in sql server 2000
declare @scores table (score int)
insert into @scores values (5);
insert into @scores values (7);
insert into @scores values (55);
insert into @scores values (6);
insert into @scores values (3);

declare @total	float;
declare @val	float;
declare @cnt	int;
declare @res	float;
set @total = 0.0;

declare cscores cursor for
select score from @scores

open cscores
fetch next from cscores into @val;
while @@FETCH_STATUS = 0
begin
select @total = @total + LOG(@val);
fetch next from cscores into @val;
end

select @cnt = COUNT(*) from @scores;
select @res = @total / (@cnt *1.0)
select @cnt, @res, @total

select EXP(@res)
0

Author Comment

ID: 34921117
Sorry, Im so new to this... .So would I create a function?  using LOSD field below, how could I Calculate Means?

This is the Length of Stay in Days, that I need to calculate Mean?
LOSD
16
21
3
8
783
1
4
4
3
13
=avg(Fields!LOSD.Value) gives me 85.60, but I need Mean?

The output will be in SSRS
0

LVL 26

Expert Comment

ID: 34921235
create function dbo.fnGetMedian ()
returns float
as
begin
declare @total	float;
declare @val	float;
declare @cnt	int;
declare @res	float;
set @total = 0.0;

declare cscores cursor for
select losd from yourTable

open cscores
fetch next from cscores into @val;
while @@FETCH_STATUS = 0
begin
select @total = @total + LOG(@val);
fetch next from cscores into @val;
end

select @cnt = COUNT(*) from @scores;
select @res = @total / (@cnt *1.0)

select @val = EXP(@res);

return @val;
end
0

LVL 26

Expert Comment

ID: 34921266
I missed to replace a point change line 22 as

select @cnt = COUNT(*) from yourTable;
0

Author Comment

ID: 34921504
Iam sorry, I am still lost.   Here is some actual data from my HPPATMFL table...
My report is grouped by unit...

INTENSIVE CARE UNIT
RoomBd      Pt Name      Station      cc Admit DT      P5EFDT      LOSD
0412 A      XXX      ICU      2/1/2011      20110216      16
0412 B      XX      ICU      1/27/2011      20110216      21
0412 C      X      ICU      2/14/2011      20110216      3
0412 D      XXX      ICU      2/9/2011      20110216      8
0412 E      XX      ICU      12/26/2008      20110216      783
0412 F      X      ICU      2/16/2011      20110216      1
0412 G      XXXXXX      ICU      2/13/2011      20110216      4
0412 H      XXXXX      ICU      2/13/2011      20110216      4
0412 I      DOE, JANE      ICU      2/14/2011      20110216      3
0412 J      DOE, JOHN      ICU      2/4/2011      20110216      13
85.60 AVERAGE
9.11 GEOMEAN

LABOR & DELIVERY
RoomBd      Pt Name      Station      cc Admit DT      P5EFDT      LOSD

0312 A      DOE, JANE      L&D      2/16/2011      20110216      1
1.00 AVERAGE
1.00 GEOMEAN
0

LVL 26

Expert Comment

ID: 34921583
so modify the function as follows and use it like this

select AVG(losd) as avarage,  dbo.fnGetMedian() as geomean
from [INTENSIVE CARE UNIT]
create function dbo.fnGetMedian ()
returns float
as
begin
declare @total	float;
declare @val	float;
declare @cnt	int;
declare @res	float;
set @total = 0.0;

declare cscores cursor for
select losd from [INTENSIVE CARE UNIT];

open cscores
fetch next from cscores into @val;
while @@FETCH_STATUS = 0
begin
select @total = @total + LOG(@val);
fetch next from cscores into @val;
end

select @cnt = COUNT(*) from [INTENSIVE CARE UNIT];
select @res = @total / (@cnt *1.0)

select @val = EXP(@res);

return @val;
end
0

Author Comment

ID: 34921971
It's looking for object [INTENSIVE CARE UNIT] and I error out....
0

Author Comment

ID: 34922063
is there a way to create a more universal function?   without having to , but more universal so when I get the mean it would be something like:   dbo.fnGetMedian(LOSD)     just a thought.
0

LVL 26

Accepted Solution

tigin44 earned 750 total points
ID: 34924163
sure you can make the funtion to be more general by providing table name and column name as input and using dynamic sql sytax or you can change funtion to an sp for using dynamic sql.
0

Author Comment

ID: 34956085
Hello,  Could you show me an example?    I haven't worked on dynamic sql much.   Thanks!
0

Author Closing Comment

ID: 35101382
I give you the points for trying.

My solution:
SELECT Exp(Sum(Log(sample))/Count(*)) AS GM
FROM sample_table;
0

## Featured Post

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataâ€¦
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" passâ€¦
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediafâ€¦
Weâ€™ve all felt that sense of false security beforeâ€”locking down external access to a database or component and feeling like weâ€™ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many wâ€¦
###### Suggested Courses
Course of the Month13 days, 1 hour left to enroll