CREATE TABLE [calculate] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[year] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[q8] [float] NOT NULL ,
	[q9] [float] NOT NULL ,
	CONSTRAINT [PK_calculate] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('First', 2, 3);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('First', 4, 2);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Second', 2, 6);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third', 1, 4);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third', 2, 3);
INSERT INTO [Test].[dbo].[calculate]([year], [q8], [q9])
VALUES('Third',9, 1);

SSELECT [year], STDEV(q8), STDEV(q9) FROM [Test].[dbo].[calculate] group by year

SELECT [year], avg(q8), avg(q9) FROM [Test].[dbo].[calculate] group by year

SELECT AVG([q8]) FROM 
( 
    SELECT [q8] FROM ( 
        SELECT TOP 1 [q8] = [q8] * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT [q8] 
            FROM [CALCULATE] ORDER BY [q8] 
        ) tab_a 
        ORDER BY 1 DESC 
    ) tab_1 
    UNION ALL 
    SELECT [q8] FROM ( 
        SELECT TOP 1 [q8] = [q8] * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT [q8] 
            FROM [CALCULATE] ORDER BY [q8] DESC 
        ) tab_b 
        ORDER BY 1 
    ) tab_2 
) median

quote:

---

Originally posted by daipayan

THANKS A LOT to both of you!

Daipayan

---

quote:

---

Originally posted by Peso

There are more efficient ways to calculate the median.
Here are two ways
http://weblogs.sqlteam.com/peterl/archive/2009/09/16/Median-and-weighted-median.aspx

---

N 56°04'39.26"
E 12°55'05.63"

---