TSQL Cross Apply with Count and Sum

tsql cross-apply
ComfortablyNumb picture ComfortablyNumb · Feb 24, 2014 · Viewed 8.5k times · Source

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?

Answer

Dumitrescu Bogdan picture Dumitrescu Bogdan · Feb 24, 2014

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.

Related questions

When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
Using CROSS APPLY for more than one column

Day #3 with SQL Server. I am trying to combine 2 columns of delimited data into one output from a Table Valued Function. Here is my data: I would like the data to be processed and placed into a table in the …

Read More
Cross Apply to get child parent value from Xml in SQL Server

I have the following XML: <root> <row value="US"> <col value="00">Jon</col> <col value="01">David</col> <col value="02">Mike</col> <col value="03"&…

Read More