How to show Median in a GROUP BY clause? (AVE works, Median doesn'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HumanJHawkins - 25 Apr 2008 19:58 GMT

We are using SQL Server 2000. I have already learned that there is no
MEDIAN, or MODE function to go along with the AVE... I've also found
several methods of computing Median. The one I like best is:

-- Apologies for not citing the source... I lost the website and the
file I downloaded has no ID in it.
SELECT
((SELECT MAX(iNumericField) FROM
(SELECT TOP 50 PERCENT iNumericField FROM dbo.MyTable ORDER BY
iNumericField) AS W1) +
(SELECT MIN(iNumericField) FROM
(SELECT TOP 50 PERCENT iNumericField FROM dbo.MyTable ORDER BY
iNumericField DESC) AS W2)
) /2 AS Median;
-- END

However, now I want to use this in a GROUP BY clause and I have no
idea how.

-- What can I replace the non-functioning Median(flData) part with?
SELECT iBin, AVG(flData) AS Mean_Data, Median(flData) AS
Median_Data
FROM dbo.MyData
GROUP BY iBin
-- END

Is there some syntax for embedding the above "Get the Median" code
into my GROUP BY query? Is there some syntax to turn it into a
function that so I can make my own "Median()" function?

Many thanks in advance!

Reply to this Message

Plamen Ratchev - 26 Apr 2008 01:21 GMT

Here is SQL Server 2000 example of grouping and using the same median
calculation. Essentially it is just adding a filter on the grouping column.
The use of MAX for the final calculation does not matter (you can replace
with MIN and get the same results).

CREATE TABLE Foo (
foo_key INT PRIMARY KEY,
foo_bin CHAR(1),
foo_value INT);

INSERT INTO Foo VALUES(1, 'a', 10);
INSERT INTO Foo VALUES(2, 'a', 20);
INSERT INTO Foo VALUES(3, 'a', 15);
INSERT INTO Foo VALUES(4, 'a', 1);
INSERT INTO Foo VALUES(5, 'a', 14);
INSERT INTO Foo VALUES(6, 'a', 3);
INSERT INTO Foo VALUES(7, 'b', 11);
INSERT INTO Foo VALUES(8, 'b', 29);
INSERT INTO Foo VALUES(9, 'b', 22);
INSERT INTO Foo VALUES(10, 'b', 1);

SELECT foo_bin, MAX(median) AS median
FROM (
SELECT F.foo_bin,
((SELECT MAX(T.foo_value)
FROM (SELECT TOP 50 PERCENT A.foo_value
FROM Foo AS A
WHERE A.foo_bin = F.foo_bin
ORDER BY A.foo_value) AS T) +
(SELECT MIN(T.foo_value)
FROM (SELECT TOP 50 PERCENT A.foo_value
FROM Foo AS A
WHERE A.foo_bin = F.foo_bin
ORDER BY A.foo_value DESC) AS T)
) / 2 AS median
FROM Foo AS F) AS X
GROUP BY foo_bin;

/*

Results:

foo_bin median
------- -----------
a 12
b 16

*/

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

HumanJHawkins - 28 Apr 2008 17:55 GMT

> Here is SQL Server 2000 example of grouping and using the same median
> calculation. Essentially it is just adding a filter on the grouping column.

<CUT>

> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks!

Reply to this Message