in my sql statement I call sum twice for the same argument, is it duplicating the effort?
No, SQL Server reuses the aggregates.
In fact, if you build the query plan, you will see the SUM in a result set of an aggregation operator (like Stream Aggregate) denoted as something like Expr****.
The value of this expression will later be used as an input to the other operators.
Here's the sample query:
SELECT ROUND(SUM(id), -1)FROM masterGROUP BY nameORDER BY SUM(id) DESCand it's plan:
|--Compute Scalar(DEFINE:([Expr1004]=round([Expr1003],(-1)))) |--Sort(ORDER BY:([Expr1003] DESC)) |--Stream Aggregate(GROUP BY:([test].[dbo].[master].[name]) DEFINE:([Expr1003]=SUM([test].[dbo].[master].[id]))) |--Index Scan(OBJECT:([test].[dbo].[master].[ix_name_desc]), ORDERED BACKWARD)As you can see, the aggregation is done once and stored in Expr1003.
Expr1003 is then reused in both the Sort operator (which processes the ORDER BY) and Compute Scalar (which processes ROUND)