DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML 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);
       *****************************************************