Hi,

Does SQL have built-in functions to do mean, median, and mode?

--

Shawn Clark

Senior Software Consultant

Meridian Technology Group

Hi,

Does SQL have built-in functions to do mean, median, and mode?

--

Shawn Clark

Senior Software Consultant

Meridian Technology Group

Shawn,

Mean = AVG.

For the others, no built-in functions.

For at least the median, I'd recommend Joe Celko's "SQL for Smarties". He has

various methods to handle the computation from a strictly relational viewpoint.

Don't remember whether he has code to compute mode.

If you could care less whether the median is computed from a strictly relational

viewpoint (most people don't care provided the number is correct), you could do

something like ...

select seq = identity (int, 1, 1), *

into #t

from (select top 100 percent from <table> order by ...) as t

select *

#t

where seq = (select min(seq) + max(seq) from #t) / 2

The above ignores the problem of even number of rows, but your SQL is good

enough to take it from this beginning point.

---------------------------------------------------------------

BP Margolin

Please reply only to the newsgroups.

When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be

cut and pasted into Query Analyzer is appreciated.

Quote:> Hi,

> Does SQL have built-in functions to do mean, median, and mode?

> --

> Shawn Clark

> Senior Software Consultant

> Meridian Technology Group

BPMargolin,

:) Doh. I new about the Mean = AVG but that is not to bad of an idea for the

median. The main thing I needed though was the mode :). I thought I would

ask about all three though. I guess I will have to figure this one out

myself. Thanks for the median though.

--

Shawn Clark

Senior Software Consultant

Meridian Technology Group

> Shawn,

> Mean = AVG.

> For the others, no built-in functions.

> For at least the median, I'd recommend Joe Celko's "SQL for Smarties". He

has

> various methods to handle the computation from a strictly relational

viewpoint.

> Don't remember whether he has code to compute mode.

> If you could care less whether the median is computed from a strictly

relational

> viewpoint (most people don't care provided the number is correct), you

could do

> something like ...

> select seq = identity (int, 1, 1), *

> into #t

> from (select top 100 percent from <table> order by ...) as t

> select *

> #t

> where seq = (select min(seq) + max(seq) from #t) / 2

> The above ignores the problem of even number of rows, but your SQL is good

> enough to take it from this beginning point.

> ---------------------------------------------------------------

> BP Margolin

> Please reply only to the newsgroups.

> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which

can be

> cut and pasted into Query Analyzer is appreciated.

> > Hi,

> > Does SQL have built-in functions to do mean, median, and mode?

> > --

> > Shawn Clark

> > Senior Software Consultant

> > Meridian Technology Group

I believe the mode is actually pretty straightforward.

Lets say you had a list of People and their ages.

Now lets say you want to get the mode, well, first off it is pretty easy to

sort by the most common ages using:

SELECT Age, Count(*) As Cnt

FROM People

GROUP BY Age

ORDER BY Cnt DESC

Basically this gives you the number of occurences for each age, from here

all you have to do is just use the first value. Ie, change the SELECT line

to:

SELECT TOP 1 Age, Count(*)

I don't remember my stat classes well, but in case you have to worry about

multiple values being the most common, then you can just use WITH TIES

Good luck

Arlen

>:) Doh. I new about the Mean = AVG but that is not to bad of an idea for

>:the

>median. The main thing I needed though was the mode :). I thought I

>would ask about all three though. I guess I will have to figure this one

>out myself. Thanks for the median though.

>--

>Shawn Clark

>Senior Software Consultant

>Meridian Technology Group

>> Shawn,

>> Mean = AVG.

>> For the others, no built-in functions.

>> For at least the median, I'd recommend Joe Celko's "SQL for Smarties".

>> He

>has

>> various methods to handle the computation from a strictly relational

>viewpoint.

>> Don't remember whether he has code to compute mode.

>> If you could care less whether the median is computed from a strictly

>relational

>> viewpoint (most people don't care provided the number is correct), you

>could do

>> something like ...

>> select seq = identity (int, 1, 1), *

>> into #t

>> from (select top 100 percent from <table> order by ...) as t

>> select *

>> #t

>> where seq = (select min(seq) + max(seq) from #t) / 2

>> The above ignores the problem of even number of rows, but your SQL is

>> good enough to take it from this beginning point.

>> ---------------------------------------------------------------

>> BP Margolin

>> Please reply only to the newsgroups.

>> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)

>> which

>can be

>> cut and pasted into Query Analyzer is appreciated.

>> > Hi,

>> > Does SQL have built-in functions to do mean, median, and mode?

>> > --

>> > Shawn Clark

>> > Senior Software Consultant

>> > Meridian Technology Group

1. Determining the median, mode and mean

Does anyone have sample code of how to determine the median, mode, and mean

using ms-sql server?

I have the following problem:

Numerous events are conducted daily throughout the year and the table

contains the Event Date, PersonID, and EventID. I wish to determine

statistical information grouped by month.

Thanks in Advance

G. Rasco

2. Table level restore is this possible

3. Median and Geometric Mean in Oracle?

8. VA-SR. TECHNO/FUNCTIONAL FINANCIALS

9. How to find Median and Mode By SQL?

10. I need help on MAX,MIN,MODE,MEAN and MEDIUM algorithms

11. Calculating means & medians in SQL Server?

12. from online mode to quiescent mode

13. How to set the ADOConnection.Mode in multithread mode

4 post • Page:**1** of **1**