I need to calculate the median value for two different columns. It is possible to do this all at once rather than calculate the median for the 1st, then the median for the 2nd? I'd like to simplify what I'm doing now if possible but haven't found a way to do it yet. I'm working in SQL 2005.
In the example below, I'm only calculating the median for measurement_a. I then do the same for measurement_b. Is there a better way?
WITH OrderRespTime AS
(
SELECT page_id, timestamp, measurement_a, measurement_b
ROW_NUMBER() OVER(PARTITION BY page_id) ORDER BY measurement_a) AS RowNum,
COUNT(*) OVER(PARTITION BY page_id) AS Cnt
FROM statTable
)
insert median_table
SELECT page_id ,@starttime, slot_id,
avg (measurement_a), measurement_b
FROM OrderRespTime
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
group by page_id
In the example below, I'm only calculating the median for measurement_a. I then do the same for measurement_b. Is there a better way?
WITH OrderRespTime AS
(
SELECT page_id, timestamp, measurement_a, measurement_b
ROW_NUMBER() OVER(PARTITION BY page_id) ORDER BY measurement_a) AS RowNum,
COUNT(*) OVER(PARTITION BY page_id) AS Cnt
FROM statTable
)
insert median_table
SELECT page_id ,@starttime, slot_id,
avg (measurement_a), measurement_b
FROM OrderRespTime
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
group by page_id
