(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 143-------------------------------------------------- How to Calculate Median ? By: srinivasma_exceldbp Date: February 5, 2015, 11:27 pm --------------------------------------------------------- --** How to Calculate Median ? CREATE TABLE dbo.Sales(SalesPerson INT, Amount INT); GO --CREATE CLUSTERED INDEX x ON dbo.Sales(SalesPerson, Amount); --CREATE NONCLUSTERED INDEX x ON dbo.Sales(SalesPerson, Amount); --DROP INDEX x ON dbo.sales; ;WITH x AS ( SELECT TOP (100) number FROM master.dbo.spt_values GROUP BY number ) INSERT dbo.Sales WITH (TABLOCKX) (SalesPerson, Amount) SELECT x.number, ABS(CHECKSUM(NEWID())) % 99 FROM x CROSS JOIN x AS x2 CROSS JOIN x AS x3; --================================================ -- SQL 2012 and above SELECT d.SalesPerson, w.Median FROM ( SELECT SalesPerson, COUNT(*) AS y FROM dbo.Sales GROUP BY SalesPerson ) AS d CROSS APPLY ( SELECT AVG(0E + Amount) FROM ( SELECT z.Amount FROM dbo.Sales AS z WHERE z.SalesPerson = d.SalesPerson ORDER BY z.Amount OFFSET (d.y - 1) / 2 ROWS FETCH NEXT 2 - d.y % 2 ROWS ONLY ) AS f ) AS w(Median); --========================================================== --** Pre SQL 2012 Methid SELECT d.SalesPerson, w.Median FROM ( SELECT SalesPerson, COUNT(*) AS y FROM dbo.Sales GROUP BY SalesPerson ) AS d CROSS APPLY ( SELECT AVG(0E + Amount) FROM ( SELECT TOP (2 - d.y % 2) Amount FROM ( SELECT TOP (d.y / 2 + 1) z.Amount FROM dbo.Sales AS z WHERE z.SalesPerson = d.SalesPerson ORDER BY z.Amount ) AS t ORDER BY Amount DESC ) AS f ) AS w(Median); *****************************************************