title: Calculating median with PERCENTILE_CONT and grouping
body: <p>I need to calculate the median number of days between the <code>oppo_opened</code> date and the <code>oppo_offered</code> date on a table called <code>Opportunity</code>, grouping results by a joined table <code>Company</code>. Doing the average is very easy:</p>
  
  <pre><code>----Average Opened to Offered last 12 months
  Select Comp_Name,AVG(DATEDIFF(day,Oppo_Opened,oppo_offerDate)) as averageDTO from company join Opportunity on oppo_lender = Comp_CompanyId
  where DATEADD(Year,-1,GETDATE()) &gt; Oppo_Opened AND oppo_offerDate is not null
  group by Comp_Name order by averageDTO desc
  </code></pre>
  
  <p>The above returns one row per company, as we are grouping by the <code>Comp_Name</code>. </p>
  
  <p>It's not quite so easy when doing this for a median. I located the following post:</p>
  
  <p><a href="https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server">Function to Calculate Median in Sql Server</a></p>
  
  <p>And that got me to here:</p>
  
  <pre><code>SELECT     Comp_Name,
      PERCENTILE_CONT(0.5) 
          WITHIN GROUP (ORDER BY DATEDIFF(day,Oppo_Opened,oppo_offerDate))
          OVER (PARTITION BY Comp_Name) AS MedianCont
  from company inner join Opportunity on oppo_lender = Comp_CompanyId
  where DATEADD(Year,-1,GETDATE()) &gt; Oppo_Opened AND oppo_offerDate is not null
  order by MedianCont desc
  </code></pre>
  
  <p>However, this returns one row per <code>Opportunity</code> record and not one row per <code>Company</code> record. I can't do a simple <code>group by Comp_Name</code> on this either as we then get:</p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Line 3</p>
    
    <p>Column 'Opportunity.Oppo_Opened' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
    
    <p>Msg 8120, Level 16, State 1, Line 3</p>
    
    <p>Column 'Opportunity.oppo_offerDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>And putting an aggregate method on either of the above I am presuming will screw up my median calculation. </p>
  
  <p><strong>My Question:</strong> How can I group by the <code>PERCENTILE_CONT</code> partition to get one row per <code>Company</code> (I should get 209 rows) as opposed to one row per <code>Opportunity</code> (north of 30k rows)?</p>