Calculate Median

5 Jan 2006 6:31 PM

Craig

I'm using SQL 2005. I would like to calculate the median (middle value in a
dataset) in either transact SQL or by using a function. Any suggestions?

Thanks.

--
Craig

5 Jan 2006 6:33 PM

Raymond D'Anjou

Google "calculate median sql server"
Here's one link I found:
http://www.tek-tips.com/faqs.cfm?fid=4751

Show quote

"Craig" <Cr***@discussions.microsoft.com> wrote in message
news:31997235-C923-47C0-9344-8F294834DAD6@microsoft.com...
> I'm using SQL 2005. I would like to calculate the median (middle value in
> a
> dataset) in either transact SQL or by using a function. Any suggestions?
>
> Thanks.
> --
> Craig

5 Jan 2006 7:04 PM

Raymond D'Anjou

"Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message
news:%23Jb7yaiEGHA.1240@TK2MSFTNGP09.phx.gbl...
> Google "calculate median sql server"
> Here's one link I found:
> http://www.tek-tips.com/faqs.cfm?fid=4751

I just saw Aaron's response.
I have to remember to always check if the poster is using SQL server 2005.
Google "calculate median sql server 2005" to get 2005 specific solutions.

5 Jan 2006 6:41 PM

Anith Sen

There are several approaches posted in this group's archives, here is one:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/8e2f9250fc56975e?dmode=source
If you looking to make use of the same logic in a UDF, see:
http://groups.google.com/group/comp.databases.ms-sqlserver/msg/df923deef078e8de?dmode=source
For a t-SQL specific method, refer to:
http://groups.google.com/group/microsoft.public.sqlserver.clients/msg/7ae885a8ba08f91b?dmode=source

--
Anith

5 Jan 2006 6:45 PM

Aaron Bertrand [SQL Server MVP]

These suggestions are not 2005 specific, FWIW.
http://www.aspfaq.com/2506

Show quote

"Craig" <Cr***@discussions.microsoft.com> wrote in message
news:31997235-C923-47C0-9344-8F294834DAD6@microsoft.com...
> I'm using SQL 2005. I would like to calculate the median (middle value in
> a
> dataset) in either transact SQL or by using a function. Any suggestions?
>
> Thanks.
> --
> Craig

5 Jan 2006 8:45 PM

Dieter Noeth

Craig wrote:

> I'm using SQL 2005. I would like to calculate the median (middle value in a
> dataset) in either transact SQL or by using a function. Any suggestions?

I use this for my Teradata trainings, should run in SS 2005 without
modification:

CREATE TABLE median_test
(
   id INTEGER
   ,med_group INTEGER
   ,med_value INTEGER
) ;

INSERT INTO median_test VALUES (1, 1, 1);
INSERT INTO median_test VALUES (2, 1, 2);
INSERT INTO median_test VALUES (3, 1, 3);
INSERT INTO median_test VALUES (4, 1, 5);

INSERT INTO median_test VALUES (5, 2, 1);
INSERT INTO median_test VALUES (6, 2, 6);
INSERT INTO median_test VALUES (7, 2, 7);

INSERT INTO median_test VALUES (8, 3, 1);

INSERT INTO median_test VALUES (9, 4, 4);
INSERT INTO median_test VALUES (10, 4, 5);
INSERT INTO median_test VALUES (11, 4, 6);
INSERT INTO median_test VALUES (12, 4, 6);
INSERT INTO median_test VALUES (13, 4, 7);
INSERT INTO median_test VALUES (14, 4, 7);
INSERT INTO median_test VALUES (15, 4, 7);
INSERT INTO median_test VALUES (16, 4, 8);

/*** "financial median" using Group COUNT + ROW_NUMBER ***/
SELECT
   dt1.med_group
   ,AVG(med_value)
FROM
(SELECT
     med_group
     ,med_value
     ,ROW_NUMBER() OVER (PARTITION BY med_group
                   ORDER BY med_value) AS row_num
     ,COUNT(*) OVER (PARTITION BY med_group) AS row_count
   FROM median_test
) AS dt1
WHERE
   row_num = (row_count + 1) / 2
OR
   row_num = (row_count / 2) + 1
GROUP BY med_group
ORDER BY med_group
;

/*** "statistical median" using Group COUNT + ROW_NUMBER ***/
SELECT
   dt1.med_group
   ,med_value
FROM
(SELECT
     med_group
     ,med_value
     ,ROW_NUMBER() OVER (PARTITION BY med_group
                   ORDER BY med_value) AS row_num
     ,COUNT(*) OVER (PARTITION BY med_group) AS row_count
   FROM median_test
) AS dt1
WHERE
-- row_num = (row_count + 1) / 2 --left (lesser) value
   row_num = (row_count / 2) + 1 --right (greater) value
ORDER BY med_group
;

Dieter

5 Jan 2006 9:16 PM

Craig

Thanks for all the suggestions...

--
Craig

Show quote

"Dieter Noeth" wrote:

> Craig wrote:
>
> > I'm using SQL 2005. I would like to calculate the median (middle value in a
> > dataset) in either transact SQL or by using a function. Any suggestions?
>
> I use this for my Teradata trainings, should run in SS 2005 without
> modification:
>
> CREATE TABLE median_test
> (
>    id INTEGER
>    ,med_group INTEGER
>    ,med_value INTEGER
> ) ;
>
>
> INSERT INTO median_test VALUES (1, 1, 1);
> INSERT INTO median_test VALUES (2, 1, 2);
> INSERT INTO median_test VALUES (3, 1, 3);
> INSERT INTO median_test VALUES (4, 1, 5);
>
> INSERT INTO median_test VALUES (5, 2, 1);
> INSERT INTO median_test VALUES (6, 2, 6);
> INSERT INTO median_test VALUES (7, 2, 7);
>
> INSERT INTO median_test VALUES (8, 3, 1);
>
> INSERT INTO median_test VALUES (9, 4, 4);
> INSERT INTO median_test VALUES (10, 4, 5);
> INSERT INTO median_test VALUES (11, 4, 6);
> INSERT INTO median_test VALUES (12, 4, 6);
> INSERT INTO median_test VALUES (13, 4, 7);
> INSERT INTO median_test VALUES (14, 4, 7);
> INSERT INTO median_test VALUES (15, 4, 7);
> INSERT INTO median_test VALUES (16, 4, 8);
>
>
> /*** "financial median" using Group COUNT + ROW_NUMBER ***/
> SELECT
>    dt1.med_group
>    ,AVG(med_value)
> FROM
>   (SELECT
>      med_group
>      ,med_value
>      ,ROW_NUMBER() OVER (PARTITION BY med_group
>                    ORDER BY med_value) AS row_num
>      ,COUNT(*) OVER (PARTITION BY med_group) AS row_count
>    FROM median_test
> ) AS dt1
> WHERE
>    row_num = (row_count + 1) / 2
> OR
>    row_num = (row_count / 2) + 1
> GROUP BY med_group
> ORDER BY med_group
> ;
>
>
> /*** "statistical median" using Group COUNT + ROW_NUMBER ***/
> SELECT
>    dt1.med_group
>    ,med_value
> FROM
>   (SELECT
>      med_group
>      ,med_value
>      ,ROW_NUMBER() OVER (PARTITION BY med_group
>                    ORDER BY med_value) AS row_num
>      ,COUNT(*) OVER (PARTITION BY med_group) AS row_count
>    FROM median_test
> ) AS dt1
> WHERE
> -- row_num = (row_count + 1) / 2 --left (lesser) value
>    row_num = (row_count / 2) + 1 --right (greater) value
> ORDER BY med_group
> ;
>
>
> Dieter
>