Median, Min and Max - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Median, Min and Max

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-01 : 05:45:47

hi

How do show all 3 Median, Min Max?

DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(9)
INSERT INTO @Temp VALUES(10)
INSERT INTO @Temp VALUES(NULL)

SELECT ((
SELECT TOP 1 DATA
FROM (
SELECT TOP 50 PERCENT DATA
FROM @Temp
WHERE DATA IS NOT NULL
ORDER BY DATA
) AS A
ORDER BY DATA DESC) +
(
SELECT TOP 1 DATA
FROM (
SELECT TOP 50 PERCENT DATA
FROM @Temp
WHERE DATA IS NOT NULL
ORDER BY DATA DESC
) AS A
ORDER BY DATA ASC)) / 2

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 05:58:31

[code]

DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(2)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(5)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(6)
INSERT INTO @Temp VALUES(7)
INSERT INTO @Temp VALUES(9)
INSERT INTO @Temp VALUES(10)
INSERT INTO @Temp VALUES(NULL)

SELECT 'Median' AS [Metric], (
(
SELECT TOP 1 DATA
FROM (
SELECT TOP 50 PERCENT DATA
FROM @Temp
WHERE DATA IS NOT NULL
ORDER BY DATA
)
AS A
ORDER BY DATA DESC
)
+
(
SELECT TOP 1 DATA
FROM (
SELECT TOP 50 PERCENT DATA
FROM @Temp
WHERE DATA IS NOT NULL
ORDER BY DATA DESC
) AS A
ORDER BY DATA ASC
)
) / 2 AS [Value]

UNION SELECT 'Max', MAX([DATA]) FROM @temp
UNION SELECT 'Min', MIN([DATA]) FROM @temp
[/code]
There is probably a nicer way to find the MEDIAN value -- I'm sure someone will have written a nice function for it.

Does anyone knwo: Can you make your own aggregate functions in MS sql yet?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 07:08:07

There is probably a nicer way to find the MEDIAN value -- I'm sure someone will have written a nice function for it.

Well you can simplify the original query posted by OP to something like this


SELECT
(
	(SELECT MAX(DATA) FROM
	  (SELECT TOP 50 PERCENT  DATA FROM @Temp ORDER BY id) AS T1)
	+
	(SELECT MIN(DATA) FROM
	  (SELECT TOP 50 PERCENT  DATA FROM @Temp ORDER BY id DESC) AS T2)
)/2

PBUH

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-10 : 02:05:00

hi

I can't get the median correct for each group. How should i go about it? thanks

DECLARE @Temp TABLE(Id INT IDENTITY(1,1),Grp Nvarchar(50), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES('grpA',1)
INSERT INTO @Temp VALUES('grpB',2)
INSERT INTO @Temp VALUES('grpC',5)
INSERT INTO @Temp VALUES('grpD',5)
INSERT INTO @Temp VALUES('grpA',5)
INSERT INTO @Temp VALUES('grpB',6)
INSERT INTO @Temp VALUES('grpC',6)
INSERT INTO @Temp VALUES('grpD',6)
INSERT INTO @Temp VALUES('grpA',7)
INSERT INTO @Temp VALUES('grpB',9)
INSERT INTO @Temp VALUES('grpC',10)
INSERT INTO @Temp VALUES('grpD',11)
INSERT INTO @Temp VALUES('grpA',11)
INSERT INTO @Temp VALUES('grpB',12)
INSERT INTO @Temp VALUES('grpC',9)
INSERT INTO @Temp VALUES('grpD',11)
INSERT INTO @Temp VALUES(NULL,NULL)
declare @Median as DECIMAL(10,2)
set @Median = (
SELECT
(
(SELECT MAX(DATA) FROM
(SELECT TOP 50 PERCENT DATA FROM @Temp ORDER BY id) AS T1)
+
(SELECT MIN(DATA) FROM
(SELECT TOP 50 PERCENT DATA FROM @Temp ORDER BY id DESC) AS T2)
)/2
)

Select @Median as [AVG] ,MIN(DATA) as [MIN],MAX(DATA)as [MAX] FROM @Temp
group by Grp

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-10 : 12:28:52

quote:

I can't get the median correct for each group. How should i go about it? thanks

DECLARE @Temp TABLE(Id INT IDENTITY(1,1),Grp Nvarchar(50), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES('grpA',1)
INSERT INTO @Temp VALUES('grpB',2)
INSERT INTO @Temp VALUES('grpC',5)
INSERT INTO @Temp VALUES('grpD',5)
INSERT INTO @Temp VALUES('grpA',5)
INSERT INTO @Temp VALUES('grpB',6)
INSERT INTO @Temp VALUES('grpC',6)
INSERT INTO @Temp VALUES('grpD',6)
INSERT INTO @Temp VALUES('grpA',7)
INSERT INTO @Temp VALUES('grpB',9)
INSERT INTO @Temp VALUES('grpC',10)
INSERT INTO @Temp VALUES('grpD',11)
INSERT INTO @Temp VALUES('grpA',11)
INSERT INTO @Temp VALUES('grpB',12)
INSERT INTO @Temp VALUES('grpC',9)
INSERT INTO @Temp VALUES('grpD',11)
INSERT INTO @Temp VALUES(NULL,NULL)
declare @Median as DECIMAL(10,2)
set @Median = (
SELECT
(
(SELECT MAX(DATA) FROM
(SELECT TOP 50 PERCENT DATA FROM @Temp ORDER BY id) AS T1)
+
(SELECT MIN(DATA) FROM
(SELECT TOP 50 PERCENT DATA FROM @Temp ORDER BY id DESC) AS T2)
)/2
)

Select @Median as [AVG] ,MIN(DATA) as [MIN],MAX(DATA)as [MAX] FROM @Temp
group by Grp

I am not sure understanding your problem correctly..... just run scripts below to see if they work for you.

select grp, max(data) ma, min(data) mi, convert(decimal(10,2),(max(data)+min(data))/2) as av from @Temp where grp is not null group by Grp

or

select grp, max(data) ma, min(data) mi, convert(decimal(10,2), avg(data)) as av from @Temp where grp is not null group by Grp

-- result
grp ma mi av
grpA 11.00 1.00 6.00
grpB 12.00 2.00 7.00
grpC 10.00 5.00 7.50
grpD 11.00 5.00 8.00

--or

grp ma mi av
grpA 11.00 1.00 6.00
grpB 12.00 2.00 7.25
grpC 10.00 5.00 7.50
grpD 11.00 5.00 8.25

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-16 : 11:39:11

Peso has a great article on this subject:

[url]http://sqlblog.com/blogs/peter_larsson/archive/2009/09/18/median-and-weighted-median-calculations.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources