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?
-
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 WilliamsCommented Nov 23, 2015 at 22:47
-
1This might be helpful: stackoverflow.com/questions/1342898/…– Kamil GosciminskiCommented Nov 23, 2015 at 22:51
Add a comment
|
1 Answer
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
