?
Solved

Get GEOMETRIC MEANS SQL

Posted on 2011-02-17
11
Medium Priority
?
901 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 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