SQL function to calculate median

Question

Possible Duplicate:
Function to Calculate Median in Sql Server

I have a table containing two field (more, but not relevant). The fields are Price and Quantity. I want to find several statistically data for this table, and among them is median price when adjusted to quantity.

Today I have a basic-slow-not so good looking function in place that takes the total Quantity and divide by 2. Then I walk the records ordered by price and subtracting the quantity from the total quantity. When the total quantity reached 0, I have my median.

As you may guess, this is slow... very slow. I'm looking for ideas on how to make this better, faster and more super-duper.

I use MS SQL Express 2008...

Answer 1

This should at least do it with only two queries instead of a loop:

declare @total int

select @total = sum(Quantity) from Products

select top 1 p.Price
from Products p
order by abs((select sum(Quantity) from Products where Price < p.Price) - (@total / 2))

An index on Price with Quantity as included field would probably to wonders for the performance...

Answer 2

I'm not near my SQL box to dig through the OVER PARTITION stuff, but taking advantage of it should do what you're looking for. Check out the SQL 2005 section of this article for examples: link text

Answer 3

This is probably the best one I've used in the past without using views:

SELECT AVG(DISTINCT Quantity)
   FROM (SELECT F1.ID, F1.Quantity,
      SUM(CASE WHEN F2.Quantity < F1.Quantity THEN 1 ELSE 0 END),
      SUM(CASE WHEN F2.Quantity = F1.Quantity THEN 1 ELSE 0 END),
      SUM(CASE WHEN F2.Quantity > F1.Quantity THEN 1 ELSE 0 END)
      FROM [Table] as F1, [Table] as F2
      GROUP BY F1.ID, F1.Quantity) AS Partitions (ID, Quantity, Lesser, Equal, Greater)
   WHERE Lesser = Greater
   OR (Lesser <= (SELECT COUNT(*) FROM [Table])/2.0 AND Greater <= (SELECT COUNT(*) FROM [Table])/2.0);