SQL Server - Reuse the value returned by the subquery without executing the subquery again

Question

I use a subquery in my select query which gives me an aggregate value, the subquery is of 4-5 lines and involves joins and conditions.

Now i need to use this value returned by the subquery in my outer query to do some calculation.

SELECT S.[SecurityId], S.[Service], (select count(Id) ----joins and conditions---- where Id = S.[SecurityId]) as [Total]
FROM TableName S

Now in my outer query i would like to use [Total] value returned and do some more calculation, lets say [Total] + 100 AS [Summed]

How do i access the subquery value in my outer query ? I dont want to execute the subquery again and again, want to reuse the value it returned the 1st time

"I dont want to execute the subquery again and again" - just because the same subquery appears textually multiple times in a query, that does **not** mean that the server will necessarily execute it multiple times. In SQL you tell the system *what you want*, not *how to do it*. — Damien_The_Unbeliever, Dec 15 '20 at 15:47

score 0 · Accepted Answer · answered Dec 15 '20 at 15:31

0

This sounds like a lateral join:

SELECT s.[SecurityId], s.[Service], x.[Total], x.[Total] + 100 AS [Summed]
FROM TableName s
OUTER APPLY (
    SELECT count(Id) AS [Total]
    ---- joins and conditions---- 
    WHERE Id = S.[SecurityId]
) x

answered Dec 15 '20 at 15:31

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Dec 15 '20 at 16:01

You can use the left join as follows:

SELECT S.[SecurityId], S.[Service], 
       t.[total] + 100 -- as per your requirement
  FROM TableName S
  LEFT JOIN (select id, count(Id) as [total]
             ----joins and conditions----
             group by Id) as T
    On s.[SecurityId] =  T.id

SQL Server - Reuse the value returned by the subquery without executing the subquery again

2 Answers2