Very Computer

by **Shawn Clar** » Wed, 31 May 2000 04:00:00

Hi,

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

--
Shawn Clark
Senior Software Consultant
Meridian Technology Group

by **BPMargoli** » Wed, 31 May 2000 04:00:00

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

by **Shawn Clar** » Wed, 31 May 2000 04:00:00

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

by **Hector** » Wed, 31 May 2000 04:00:00

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

Very Computer

Mean, Median, Mode

Mean, Median, Mode

Mean, Median, Mode

Mean, Median, Mode

Mean, Median, Mode