Search code examples
t-sqlcross-apply

TSQL Cross Apply with Count and Sum

I've just discovered The CROSS APPLY operator and was under the impression it was very useful in manipulating derived columns for calculations.

So, I tried the following:

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

But I get the following error:

Aggregates on the right side of an APPLY cannot reference columns from the left side.

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?

Solution

  • Cross apply works at line level.

    "For each line on the left, we apply a function on the right".

    Your aggregation has no meaning on per line bases.