Solved

Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
11
890 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
Independent Software Vendors: 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!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

688 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