Calculating Median Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculating Median Help

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-02-20 : 23:00:58

I'm having some trouble calculating the Median correctly. I'm able to calculate the median correctly if there is an even number of rows; however, when there is an odd number of rows, it is not calculating correctly. Could someone take a look at the code below and let me know where I am going wrong. The code is kind of a mess, but you can see in the median Column that it is not calculating correctly. The median is being calculated based on the total column which is grouped by orderdate and salespersonID. Any help would be greatly appreciated.

use adventureworks

;WITH CTE AS (
select convert(varchar(10),so.OrderDate,101) as Order_Date, sp.SalesPersonID, sod.SalesOrderID, sum(sod.OrderQty) as Total
,

(
select sum(sod1.orderqty)
from Sales.SalesOrderDetail sod1
join sales.SalesOrderHeader so1
on sod1.SalesOrderID = so1.SalesOrderID
join
sales.SalesPerson sp1
on so1.SalesPersonID = sp1.SalesPersonID
where so1.OrderDate = so.OrderDate
and sp1.SalesPersonID = sp.SalesPersonID
) as GrandTotal

,
(
select COUNT(*)
from Sales.SalesOrderDetail sod1
join sales.SalesOrderHeader so1
on sod1.SalesOrderID = so1.SalesOrderID
join
sales.SalesPerson sp1
on so1.SalesPersonID = sp1.SalesPersonID
where so1.OrderDate = so.OrderDate
and sp1.SalesPersonID = sp.SalesPersonID
) as COUNT1

from sales.SalesOrderHeader so inner join Sales.SalesOrderDetail sod
on so.SalesOrderID = sod.SalesOrderID
join
sales.SalesPerson sp
on so.SalesPersonID = sp.SalesPersonID

group by so.OrderDate, sp.SalesPersonID, sod.SalesOrderID

)

, CTE2

as (
select CTE.Order_Date, CTE.SalesPersonID, CTE.SalesOrderID, CTE.Total, CTE.GrandTotal
, (SELECT COUNT(*) FROM CTE AS CTE1 WHERE CTE1.Order_Date = CTE.Order_Date AND CTE1.SalesPersonID = CTE.SalesPersonID) AS Count1,
CTE.GrandTotal / (SELECT COUNT(*) FROM CTE AS CTE1 WHERE CTE1.Order_Date = CTE.Order_Date AND CTE1.SalesPersonID = CTE1.SalesPersonID) as average
from CTE

group by CTE.Order_Date, CTE.SalesPersonID, CTE.SalesOrderID, CTE.Total, CTE.GrandTotal

)

select CTE2.Order_Date, CTE2.SalesPersonID, CTE2.SalesOrderID, CTE2.GrandTotal, CTE2.Total,
CTE2.average, CTE2.Count1 , (select MAX(UpperLimit.total) from (select top 50 percent CTE3.total from CTE2 as CTE3 where CTE3.Order_Date = CTE2.Order_Date and CTE3.SalesPersonID = CTE2.SalesPersonID order by CTE3.total) as UpperLimit) +
(select min(LowerLimit.total) from (select top 50 percent CTE3.total from CTE2 as CTE3 where CTE3.Order_Date = CTE2.Order_Date and CTE3.SalesPersonID = CTE2.SalesPersonID order by CTE3.total) as LowerLimit)
/ 2 AS Median

from CTE2
group by CTE2.Order_Date, CTE2.SalesPersonID, CTE2.SalesOrderID, CTE2.GrandTotal, CTE2.Total,
CTE2.average, CTE2.Count1
order by CTE2.Order_Date, CTE2.SalesPersonID, CTE2.SalesOrderID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:25:35

you are calculating median for whole dataset or for some subgroup within it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources