title: Calculate grand median in SQL Server 2012
body: <p>Let's say I want to calculate the grand median for a table for a continuous column X. One could use this code snippet:</p>
  
  <pre><code>PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY X)  
      OVER (PARTITION BY 
          ?
      )
  AS grand_median 
  </code></pre>
  
  <p>The OVER PARTITION BY part is mandatory though. Why is this when I want to calculate the grand median? What can I do please? Thanks!</p>
  
  <p>PS: just to add some artificial data - already with answer inspired by @Paweł Dyl</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#Data') IS NOT NULL
      DROP TABLE #Data
  
  CREATE TABLE #Data
  (
      Number FLOAT,
  )
  
  INSERT INTO #Data (Number) VALUES (30);
  INSERT INTO #Data (Number) VALUES (20);
  INSERT INTO #Data (Number) VALUES (42);
  INSERT INTO #Data (Number) VALUES (42);
  INSERT INTO #Data (Number) VALUES (42);
  INSERT INTO #Data (Number) VALUES (43);
  INSERT INTO #Data (Number) VALUES (40);
  
  SELECT * FROM #Data
  
  SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Number) OVER() FROM #Data
  </code></pre>
  
  <p>Some R code to 'test' this:</p>
  
  <pre><code>test &lt;- c(30, 20, 42, 42, 42, 42, 40)
  median(test)
  </code></pre>
  
  <p>The correct answer is of course 42.</p>