Solved

Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
11
885 Views
Last Modified: 2012-05-11
Similar to GEOMEAN in excel, I am try ing to get the Mean using SQL2000 and SSRS.  Could somebody help me with this?
0
Comment
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
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:tigin44
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)

Open in new window

0
 

Author Comment

by:epicazo
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

by:tigin44
ID: 34921235
A function like this should help you
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

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 26

Expert Comment

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

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

Author Comment

by:epicazo
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

by:tigin44
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

Open in new window

0
 

Author Comment

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

Author Comment

by:epicazo
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

by:
tigin44 earned 250 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

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

Author Closing Comment

by:epicazo
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question