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?

Naughty Ninja
  • 1,635
  • 7
  • 19
  • 51
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
  • 62,698
  • 12
  • 85
  • 116