title: TSQL Cross Apply with Count and Sum
body: <p>I've just discovered The CROSS APPLY operator and was under the impression it was very useful in manipulating derived columns for calculations.</p>
  
  <p>So, I tried the following:</p>
  
  <pre><code>SELECT leadYear,TotalLeadsCalled,SuccessLeadsCalled,SuccessLeadsPercent
  FROM dbo.tblBinOutboundCallActivity 
  CROSS APPLY(VALUES (YEAR(leadactivitydate))) AS a1(leadyear) 
  CROSS APPLY(VALUES (COUNT(leadStatusID))) AS a2(TotalLeadsCalled) 
  CROSS APPLY(VALUES (COUNT(CASE WHEN leadStatusID = 2 THEN 1 ELSE NULL END))) AS a3(SuccessLeadsCalled) 
  CROSS APPLY(VALUES (SUM((SuccessLeadsCalled/TotalLeadsCalled)*100))) AS a4(SuccessLeadsPercent) 
  GROUP BY leadYear
  </code></pre>
  
  <p>But I get the following error:</p>
  
  <blockquote>
    <p>Aggregates on the right side of an APPLY cannot reference columns from
    the left side.</p>
  </blockquote>
  
  <p>I don't really understand the error, especially as the first cross apply actually works in isolation. Have I just got the concept completely wrong?</p>