How to get the median value in SQL Result?

The MEDIAN calculus is not actually an aggregate standard function.

But you can simualte it like this :

************** 1 all values are differents *******************

1) if all values are different (no double such like "SELECT ThcColumn ..." <=>
"SELECT DISTINCT ThcColumn ..."
then the query can be write as this :

CREATE TABLE T_STATISTIQUES_STT
(STT_ID INT,
STT_VALEUR FLOAT NOT NULL)

INSERT INTO T_STATISTIQUES_STT VALUES (1, 22.0)
INSERT INTO T_STATISTIQUES_STT VALUES (2, 27.5)
INSERT INTO T_STATISTIQUES_STT VALUES (3, 22.5)
INSERT INTO T_STATISTIQUES_STT VALUES (4, 24.0)
INSERT INTO T_STATISTIQUES_STT VALUES (5, 23.0)
INSERT INTO T_STATISTIQUES_STT VALUES (6, 23.5)

The median value is :
STT_ID STT_VALEUR
----------- -----------------------------------------------------
1 22.0
3 22.5
5 23.0
<-- MEDIAN VALUE : AVG of 23.0 and 23.5 = 23.25
6 23.5
4 24.0
2 27.5

The query to do this :

SELECT AVG(STT_VALEUR) AS MEDIANE
FROM (SELECT STT_VALEUR
FROM T_STATISTIQUES_STT STT
WHERE ABS((SELECT COUNT(*)
FROM T_STATISTIQUES_STT SOU
WHERE SOU.STT_VALEUR < STT.STT_VALEUR) -
(SELECT COUNT(*)
FROM T_STATISTIQUES_STT SUR
WHERE SUR.STT_VALEUR > STT.STT_VALEUR)) <= 1 ) T

************** 2 some values are identical *******************

Add to the above table theses lines :

INSERT INTO T_STATISTIQUES_STT VALUES (7, 22.0)
INSERT INTO T_STATISTIQUES_STT VALUES (8, 22.0)
INSERT INTO T_STATISTIQUES_STT VALUES (9, 22.0)
INSERT INTO T_STATISTIQUES_STT VALUES (10, 22.0)
INSERT INTO T_STATISTIQUES_STT VALUES (11, 22.0)

Now the median is :

STT_ID STT_VALEUR
----------- -----------------------------------------------------
1 22.0
10 22.0
11 22.0
8 22.0
7 22.0

9 22.0 <-- MEDIAN value : 22.0

3 22.5
5 23.0
6 23.5
4 24.0
2 27.5

The query to do this :

SELECT AVG(DISTINCT STT_VALEUR) AS MEDIANE
FROM (SELECT STT_VALEUR
FROM (SELECT *
FROM T_STATISTIQUES_STT
UNION ALL
SELECT *
FROM T_STATISTIQUES_STT) STT
WHERE (SELECT COUNT(*)
FROM T_STATISTIQUES_STT) < (SELECT COUNT(*)
FROM (SELECT *
FROM T_STATISTIQUES_STT
UNION ALL
SELECT *
FROM T_STATISTIQUES_STT) AS SOU
WHERE SOU.STT_VALEUR <= STT.STT_VALEUR)
AND (SELECT COUNT(*)
FROM T_STATISTIQUES_STT) < (SELECT COUNT(*)
FROM (SELECT *
FROM T_STATISTIQUES_STT
UNION ALL
SELECT *
FROM T_STATISTIQUES_STT) AS SUR
WHERE SUR.STT_VALEUR >= STT.STT_VALEUR) ) T

A +

--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************


Jack a écrit:

Hello :

I meet a trouble.
I want get a median value from a sql query

Question:
I use a sql query: "Select values from tablename "
so i get follow :
Values
1
2
3
4
5
6
7

Question is how to write a sql query get the "4" only a median value ??
(select median(values) from a tablename is wrong sql :( )
--
Regards

Jack.Chen

mail:chenwei@vst-china.com