I am trying to expand upon and already useful median query in SQL. I need to figure out where to put a GROUP BY statement so I can get the median "length of stay" grouped by a field in the table called "TSheetTitleDescription". The folowing is the median query that works flawlessly:
SELECT Avg([R1]) AS Median
FROM [
SELECT Max(Q1.R2) AS R1
FROM
(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2
FROM LOS_min_amb_work
ORDER BY LOS_min_amb_work.los ASC) AS Q1
UNION
SELECT Min(Q2.R2) AS R1
FROM
(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2
FROM los_min_amb_work
ORDER BY LOS_min_amb_work.los DESC) AS Q2
]. AS Q3;
Question is where to put the group by in this query. And I suspect I need to define in the query structure that I want the filed in the query.
Any help would be greatly appreciated.
SELECT Avg([R1]) AS Median
FROM [
SELECT Max(Q1.R2) AS R1
FROM
(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2
FROM LOS_min_amb_work
ORDER BY LOS_min_amb_work.los ASC) AS Q1
UNION
SELECT Min(Q2.R2) AS R1
FROM
(SELECT TOP 50 PERCENT LOS_min_amb_work.los AS R2
FROM los_min_amb_work
ORDER BY LOS_min_amb_work.los DESC) AS Q2
]. AS Q3;
Question is where to put the group by in this query. And I suspect I need to define in the query structure that I want the filed in the query.
Any help would be greatly appreciated.