CODEWITHSUNDEEP
sqlsql-server
Maryam Yasaei
Maryam Yasaei

Reputation: 23

Finding Median in SQL Server in first 10 rows

SELECT
    [RowAsc] AS RowAsc,
    [RowDesc] AS RowDesc,
    UnitRate 
FROM
    (SELECT TOP(10)
         JobName AS JobName, Client AS Client,
         AssetClass AS AssetClass, AssetType AS AssetType,    
         AssetSubType AS AssetSubType,
         Component AS Component, 
         ComponentType AS ComponentType, ComponentSubType AS ComponentSubType,
         UnitRate AS UnitRate,
         ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType 
                            ORDER BY UnitRate ASC) AS [RowAsc],
         ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType 
                            ORDER BY UnitRate DESC) AS [RowDesc]
     FROM 
         [dbo].[ReplaceCost_DirectCost_Details] rdd
     WHERE
         client = 'APV_Ballina_Shire_Council_Old'  
         AND UnitRate IS NOT NULL
         AND UnitRate <> 0) x
WHERE    
    RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

I have this script to get the median of UnitRate but it gives me wrong result for median.I have 10 rows and want to get the median of UnitRate for those 10 rows. Thanks in advance for your help.

Upvotes: 1

Views: 158

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

For an even number of rows, you need a way to return one row. One method is avg():

Select avg(UnitRate)
from . . .

This is all that is needed in the outer query.

Upvotes: 1

user4843530
user4843530

Reputation:

Run the inner query by itself, and modify it until you get the correct results (I was going to say the results you expect, but that is not necessarily correct). Allow the process of refining that query to show you if your expectations are correct or not. Run it with the top 10 constraint and without it to see what that is doing, and possibly add an order by clause that will get you the right top 10. Once you have that inner query running appropriately and giving you the correct results, add the outer query back in.

My suspicion is not so much that you are missing an order by, but that you have put the top 10 in the inner query instead of the outer query. You might also try moving that top 10 to the outer query, if all you want is the first 10 unit rate medians over the window attributes you have specified.

Without a better understanding of your schema and what you are trying to get at, it is impossible to give you a precise answer, so I am settling for giving you a methodology as an answer... but I am left wondering if this should be a comment.

Upvotes: 0

Related Questions