-- Решение на SQL Server 2000
USE pubs;
GO

IF OBJECT_ID('dbo.fn_median') IS NOT NULL
DROP FUNCTION dbo.fn_median;
GO

CREATE FUNCTION dbo.fn_median(@stor_id AS CHAR(4)) RETURNS NUMERIC(11, 1)
AS
BEGIN
RETURN
(
(SELECT MAX(qty) FROM
(SELECT TOP 50 PERCENT qty FROM dbo.sales
WHERE stor_id = @stor_id
ORDER BY qty) AS H1)
+
(SELECT MIN(qty) FROM
(SELECT TOP 50 PERCENT qty FROM dbo.sales
WHERE stor_id = @stor_id
ORDER BY qty DESC) AS H2)
) / 2.
END
GO

SELECT stor_id, dbo.fn_median(stor_id) AS median
FROM dbo.stores;
GO

-- Решение на SQL Server 2005
WITH salesRN AS
(
SELECT stor_id, qty,
ROW_NUMBER() OVER(PARTITION BY stor_id ORDER BY qty) AS rownum,
COUNT(*) OVER(PARTITION BY stor_id) AS cnt
FROM sales
)
SELECT stor_id, CAST(AVG(1.*qty) AS NUMERIC(11, 1)) AS median
FROM salesRN
WHERE rownum IN ((cnt+1)/2, (cnt+2)/2)
GROUP BY stor_id;
GO