Finding Median In Sql Server

I want to get the median of unitRate from [dbo].[ReplaceCost_DirectCost_Details] view in Microsoft Sql Server Management Studio. I already got Min,Max and avg of it.But do not know

Solution 1:

EDIT

SQL Fiddle

CREATE TABLE Table1
    ([somevalue] int)
;
    
INSERT INTO Table1
    ([somevalue])
VALUES
    (141),
    (325),
    (325),
    (353),
    (3166),
    (325),
    (207),
    (141),
    (3166),
    (161)
;

Query 1:

with cte as (
  select *
  , row_number() over(order by somevalue) as RowNum
  , count(*) over() as RowCnt
  from table1
  )
select
*
from CTE
WHERE   RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2) 

| somevalue | RowNum | RowCnt |
|-----------|--------|--------|
|       325 |      5 |     10 |
|       325 |      6 |     10 |

---

Please consider the following small example. There are 7 rows of data, the median is the "midpoint" of those, so the where clause uses a row number compared to row count, and returns just that midpoint valuse. That value (67) repesents the median of that small sample.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([somevalue] int)
;
    
INSERT INTO Table1
    ([somevalue])
VALUES
    (2),
    (45),
    (67),
    (89),
    (4567),
    (6),
    (1290)
;

Query 1:

with cte as (
  select *
  , row_number() over(order by somevalue) as RowNum
  , count(*) over() as RowCnt
  from table1
  )
select
*
from CTE
WHERE   RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2) 

Results:

| somevalue | RowNum | RowCnt |
|-----------|--------|--------|
|        67 |      4 |      7 |

Solution 2:

(sorry for using a second answer, but it will get lost if just added to the earlier one)

I am really not certain what the expected output of your query is. But I note that you are using TOP(10) and for that to work you must have an order by otherwise the result is indeterminate for the first 10 rows.

While the following may produce many more rows than you need, perhaps it will help lead to a solution.

WITH Basis as (
      SELECT
            JobName
          , Client
          , AssetClass
          , AssetType
          , AssetSubType
          , Component
          , ComponentType
          , ComponentSubType
          , UnitRate
          , ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType
                               ORDER BY UnitRate)
            AS [rownum]
      FROM [dbo].[ReplaceCost_DirectCost_Details] rdd
      WHERE client = 'APV_Ballina_Shire_Council_Old'
      AND UnitRate IS NOT NULL
      AND UnitRate <> 0
     )
, Top10s as (
      SELECT
            JobName
          , Client
          , AssetClass
          , AssetType
          , AssetSubType
          , Component
          , ComponentType
          , ComponentSubType
          , UnitRate
          , rownum
          , COUNT(*) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType)
            AS rowcnt
      FROM Basis
      WHERE rownum <= 10
      )
, Medians as (
      SELECT
            JobName
          , Client
          , AssetClass
          , AssetType
          , AssetSubType
          , Component
          , ComponentType
          , ComponentSubType
          , AVG(UnitRate) AS Median
      FROM Top10s
      WHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
      GROUP BY
            JobName
          , Client
          , AssetClass
          , AssetType
          , AssetSubType
          , Component
          , ComponentType
          , ComponentSubType
          , AVG(UnitRate) 
      )
SELECT
      JobName
    , Client
    , AssetClass
    , AssetType
    , AssetSubType
    , Component
    , ComponentType
    , ComponentSubType
    , UnitRate
    , rownum
    , rowcnt
    , MAX(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [maxfinalunitrate]
    , MIN(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [minfinalunitrate]
    , AVG(UnitRate) OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType) AS [meanfinalunitrate]
    , Medians.Median
FROM Top10s t
JOIN Medians m ON t.JobName = m.JobName
    AND t.Client = m.Client
    AND t.AssetClass = m.AssetClass
    AND t.AssetType = m.AssetType
    AND t.AssetSubType = m.AssetSubType
    AND t.Component = m.Component
    AND t.ComponentType = m.ComponentType
    AND t.ComponentSubType = m.ComponentSubType
;