CODEWITHSUNDEEP
sqlsql-servert-sql
Unbreakable
Unbreakable

Reputation: 8084

How to reuse an already calculated column in SELECT

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?

Upvotes: 1

Views: 2546

Answers (4)

GuidoG
GuidoG

Reputation: 12014

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.modspent
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. That could change off course when the query gets much bigger and more complicated.

There won't be any improvement to performance, so the only reason to do this is to make it more clear/readable for you

Upvotes: 3

George Menoutis
George Menoutis

Reputation: 7240

The other answers already cover some good ground, but please note:

  1. Surely you should not select into a @variable to "save" one sum and then make a new select on your table alongside with that value, because you will be scanning the table twice.

  2. I understand how one would try to optimize performance by thinking low-level (CPU operations), which would lead you to think of avoiding extra summations. However, SQL Server is a different beast. You have to learn to read the execution plan, and the data pages involved. If your code avoids uneccessary page reads, doing more cpu work (if even that happens) is very usually negligible. In layman's terms for your case: if the table has few rows, it probably isn't worth even thinking. If it has many, reading the entirety of those pages from disk (and sorting them due to the grouping by iff no index exists) will take 99.99% of time relative to adding the values for the sums

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

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

)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 2

Related Questions