How to use ROW_NUMBER() along with aggregate functions?
<p>I have the following scenario in my SQL query. I have to use <code>ROW_NUMBER()</code> in my query, where some aggregate functions also are used to get the result.</p>

<pre><code>SELECT 
    @TotalRequests = ReportCount.TotalCount,
    @TotalTimeToRespond = ReportCount.TotalTimeToRespond,
    @TotalRequestsHavingQnA = ReportCount.TotalRequestsHavingQnA,
    @ResponseCompliance = ReportCount.ResponseCompliance,
    @TotalSubmissions = ReportCount.TotalSubmissions
FROM
    (SELECT 
         TotalCount = Count(1),
         TotalTimeToRespond = SUM(Datediff(DAY, DCR.DateReceivedInCB, DCR.DueDate)),
         TotalRequestsHavingQnA = SUM(CASE
                                         WHEN DCR.NoOfQuestionsAsked IS NULL
                                                    OR DCR.NoOfQuestionsAsked = 0 THEN 0
                                              ELSE 1
                                            END),
         ResponseCompliance = Sum(CASE
                                          WHEN DCR.NoOfQuestionsAsked IS NULL
                                                OR DCR.NoOfQuestionsAsked = 0 THEN 0
                                          ELSE ( Cast(DCR.NoOfQuestionsAnswered AS DECIMAL) * 100 ) / Cast(DCR.NoOfQuestionsAsked AS DECIMAL)
                                        END),
         TotalSubmissions = Sum(CASE
                                        WHEN DCR.DateSubmitted IS NOT NULL THEN 1
                                        ELSE 0
                                      END),
               rowNumber = Row_number()
                             OVER (
                               PARTITION BY DCR.callref
                               ORDER BY DCR.DateSubmitted DESC)
     FROM   
         DimCBComparisonReport DCR
     INNER JOIN 
         DimClientLoc DCL ON DCR.ClientLocKey = DCL.ClientLocKey
     WHERE  
         Ltrim(Rtrim(DCL.LocId)) IN (SELECT PARAM
                                     FROM Fn_splitparam(@LocationIdList, ','))
         AND (CASE
                WHEN @EnabledDateType = 'D' THEN DCR.DueDate
                       ELSE DCR.DateSubmitted
              END) BETWEEN @StartDate AND @EndDate
         AND DCL.ContractId = @ContractRef
         AND DCR.EmployeeKey IS NOT NULL) ReportCount
WHERE  
    ReportCount.rowNumber = 1 
</code></pre>

<p>So here I am getting an exception like :</p>

<blockquote>
  <p>Msg 8120, Level 16, State 1, Procedure etl_CaseBuilder_get_Location_Compliance_Summary, Line 41<br>
  Column 'DimCBComparisonReport.CallRef' is invalid in the select list because
  it is not contained in either an aggregate function or the GROUP BY
  clause.</p>
</blockquote>

<p>Any help will be deeply appreciated.</p>