0

Query

SELECT SpaID, COUNT(1) AS Total FROM dbo.[Order] (nolock) 

WHERE DateCreated BETWEEN '04-01-2014' AND '04-30-2014'

GROUP BY SpaID

I need to find the median amount in the new column named "Total."
Is anyone aware of how to do this?

threesixnine
  • 1,733
  • 7
  • 30
  • 56
John Williams
  • 11
  • 3
  • The result set for the query above will give me 7366 results. I need to order these results and find out which of these rows if the middle of the result set. – John Williams Nov 23 '15 at 22:47
  • 1
    This might be helpful: http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server – Kamil Gosciminski Nov 23 '15 at 22:51

1 Answers1

0
WITH X AS 
(
 SELECT * --SpaID, COUNT(1) AS Total 
 FROM dbo.[Order] (nolock) 
 WHERE DateCreated BETWEEN '04-01-2014' AND '04-30-2014'
)
SELECT 
 (
 SELECT MAX(SpaID) As Bottom50
   FROM (Select TOP (50) PERCENT SpaID FROM X ORDER BY SpaID ASC ) a
 +
  SELECT MAX(SpaID) As Top50
   FROM (Select TOP (50) PERCENT SpaID FROM X ORDER BY SpaID DESC) b
 ) / 2 AS Median
M.Ali
  • 67,945
  • 13
  • 101
  • 127