How to reuse an already calculated SELECT column?

Current query

SELECT 
    SUM(Mod),
    SUM(Mod) - SUM(Spent)
FROM 
    tblHelp 
GROUP BY 
    SourceID

Pseudo query

SELECT 
    SUM(Mod),
    USE ALREADY CALCULATED VALUE - SUM(Spent)
FROM 
    tblHelp 
GROUP BY 
    SourceID

Question: since SUM(Mod) is already calculated, can I put it in temp variable and use it in other columns in the SELECT clause? Will doing so increase the efficiency of SQL query?

CodePudding user response：

You can't, at least not directly. You can use tricks such as using a derived table or a cte or cross apply but you can't use a value computed in the select clause in the same select clause.

example:

SELECT SumMode, SumMode - SumSpent
FROM
(
SELECT 
      SUM(Mod) As SumMode,
      SUM(Spent) As SumSpent
      FROM tblHelp GROUP BY SourceID
) As DerivedTable;

It will probably not increase performance, but for complicated computation it can help with code clarity, though.

CodePudding user response：

SQL Server has a quite intelligent query parser, so while I can't prove it I would be very surprised if it didn't calculate it only once. However, you can make sure of it with:

select x.SourceId, x.Mod, x.Mod - x.Spent
from 
(
    select SourceId, sum(Mod) Mod, sum(Spent) Spent
    from tblHelp
    group by SourceId
) x

)

CodePudding user response：

A subquery could do this for you, but it won't make any difference to sql server. If you think that this would make the query more readable than go ahead, here is an example

select t.modsum ,t.modsum - t.modspend
from   ( SELECT SUM(Mod) as modsum,
                SUM(Spent) as modspent
        FROM tblHelp GROUP BY SourceID
       ) t

But, is this more readable for you than

SELECT 
      SUM(Mod),
      SUM(Mod) - SUM(Spent)
      FROM tblHelp GROUP BY SourceID

IMHO I don't find the first query more readable
There won't be any improvement to performance, so why bother ?