Solved

Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
11
872 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
  • 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now