Calculate Median

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.
--
CraigGoogle "calculate median sql server"
Here's one link I found:
http://www.tek-tips.com/faqs.cfm?fid=4751
"Craig" <Craig@.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|||There are several approaches posted in this group's archives, here is one:
http://groups.google.com/group/micr...5e?dmode=source
If you looking to make use of the same logic in a UDF, see:
http://groups.google.com/group/comp... />
ode=source
For a t-SQL specific method, refer to:
http://groups.google.com/group/micr...1b?dmode=source
Anith|||These suggestions are not 2005 specific, FWIW.
http://www.aspfaq.com/2506
"Craig" <Craig@.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|||"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.|||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|||Thanks for all the suggestions...
--
Craig
"Dieter Noeth" wrote:

> Craig wrote:
>
> 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
>sql