0
SELECT median.spaid
    ,median.total
    ,ROW_NUMBER() OVER (
        ORDER BY median.total
        ) AS row
FROM (
    SELECT SpaID
        ,COUNT(1) AS Total
    FROM dbo.[Order](NOLOCK)
    WHERE DateCreated BETWEEN '04-01-2014'
            AND '04-30-2014'
    GROUP BY SpaID
    ) AS median
ORDER BY median.total

My issue here is that I need to find the middle row for column "Total" using Row_number. I need to find which "SpaID" is linked to the middle row of the "Total" column.

Juan Carlos Oropeza
  • 44,203
  • 10
  • 63
  • 104
John Williams
  • 11
  • 3
  • Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ Aside from the question I would recommend not using that NOLOCK hint. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ And if you do at least get the proper syntax, omitting the WITH keyword is deprecated. – Sean Lange Nov 24 '15 at 17:01
  • middle row for column `Total` or `median.total` – Juan Carlos Oropeza Nov 24 '15 at 17:11
  • 1
    Possible duplicate of [Function to Calculate Median in Sql Server](http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server) – Juan Carlos Oropeza Nov 24 '15 at 17:12
  • Middle row for column median.total @JuanCarlosOropeza – John Williams Nov 24 '15 at 17:15

3 Answers3

0

This is a shot in the dark based on very sparse details but I think you are looking for something like this.

with numberedResults as
(
    select spaid
        , ROW_NUMBER() over(order by count(*)) as RowNum
    from [order]
    where DateCreated between '20140401' AND '20140630'
    group by SpaID
)
, Medians as
(
    select MAX(RowNum) / 2 as Median
        , MAX(RowNum) as TotalCount
    from numberedResults
)

select *
from numberedResults r
join Medians m on m.Median = r.RowNum
Sean Lange
  • 30,535
  • 3
  • 21
  • 37
0

Here is one method of finding the median:

SELECT o.*
FROM (SELECT SpaID, COUNT(*) AS Total,
             ROW_NUMBER() OVER (ORDER BY COUNT(*)) as seqnum,
             COUNT(*) OVER () as cnt
      FROM dbo.[Order](NOLOCK)
      WHERE DateCreated BETWEEN '2014-04-01' AND '2014-04-30'
      GROUP BY SpaID
     ) o
WHERE 2*o.seqnum IN (cnt - 1, cnt);

This is approximate when you have an even number of rows. You are looking for the exact row id, so you have to choose either the one before or after the median (which is between two rows).

Note: You should expression date constants using the ISO standard formats, either YYYYMMDD or YYYY-MM-DD. The first is the safest way in SQL Server (although I personally prefer the hyphens for readability).

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

I would suggest not relying on ROW_NUMBER in your query as results using ROW_NUMBER can at times be unpredictable. I understand it seems bulky - -the challenge is the "median" is the middle of grouped rows. Here's the query I believe should work for you:

    SELECT SpaID, d FROM
     (SELECT SpaID, 
      DENSE_RANK() OVER (ORDER BY COUNT(1)) AS d
      FROM dbo.[Order] 
      WHERE DateCreated BETWEEN '04-01-2014'
      AND '04-30-2014'
      GROUP BY SpaID)
    WHERE  D=
     (SELECT ROUND(MAX(D)/2,0)
      DENSE_RANK() OVER (ORDER BY COUNT(1)) AS d
      FROM dbo.[Order] 
      WHERE DateCreated BETWEEN '04-01-2014'
      AND '04-30-2014')
Charlie K
  • 114
  • 5
  • This query does not execute a result set. I am going to study the execution plan to see what I can do to make this query work. The error lies in the second WHERE statement. – John Williams Nov 24 '15 at 18:31
  • Nick: I developed and executed in Oracle SQL. Because all the functions are ANSI standard -- thought we'd have it beat. Please confirm the operation of the DENSE_RANK function in sql-server since that is the most "exotic" piece of the code. – Charlie K Nov 24 '15 at 22:20