Mean, Median, Mode

Question

Post reply

Mean, Median, Mode

mail2payan

SSCrazy

Points: 2286
More actions
December 3, 2009 at 5:38 am

#228293

Hello there,
I want to calculate mean, median, mode, range & standard deviation of the following table:
CREATE TABLE [calculate] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[year] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[q8] [float] NOT NULL ,
[q9] [float] NOT NULL ,
CONSTRAINT [PK_calculate] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Data in the columns are:
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('First', 2, 3);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('First', 4, 2);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Second', 2, 6);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third', 1, 4);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third', 2, 3);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third',9, 1);
Now Standard Deviation & Mean is:
SSELECT [year], STDEV(q8), STDEV(q9) FROM [Test].[dbo].[calculate] group by year
SELECT [year], avg(q8), avg(q9) FROM [Test].[dbo].[calculate] group by year
But HOW TO CALCULATE MEDIAN & MODE & RANGE??
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

One of the best work bench to the calculating the MEDIAN, is ref this

http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I'm not sure how fast the methods in that article are but I do know the method used in the following forum response is nasty fast and can easily be modified to do either a Statistical Median or a Mathematical Median. The post includes a million row test table generator, several examples, and a whole lot of explanation.

http://www.sqlservercentral.com/Forums/Topic351991-8-1.aspx#bm352218

Please be sure to read the very last post on that thread where a correction was made to one of the "observations" of the code found in a different article.

Hmmm... might be fodder for another article for comparison of performance between multiple different methods. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Just curious... did either of those links help you sort your problem out?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mail2payan SSCrazy Points: 2286 More actions · Answer 4

Yes Sir, Following link helped me a lot and sorry, I didn't replied you back!

----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Thanks, Daipayan,

Just wanted to make sure you were covered on this. Lot's of folks simply do it wrong and they end up with a huge performance problem on their hands.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)