Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vedder
Starting Member
8 Posts |
 Posted - 2004-08-28 : 10:28:26
|
| Hi. This might seem like a bit of a newbie question, but I haven't been able to find any answers to this online anywhere, so here goes.What's the most efficient way of using an aggregate function in multiple parts of a select statement? Say I have something like:SELECT Sum(MyField) As TotalMyField, Sum(MyField)/Count(MyField) As MyOtherFieldFROM MyTable WHERE MyField > 10To me, this looks like SQL Server will firstly calculate the sum of MyField and then recalculate it when doing the second part (Sum(MyField)/Count(MyField))? If not, sorry for wasting your time, but if it is, can anyone suggest a more efficient way of doing this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-28 : 11:38:18
|
| For that the optimiser will probably do the sums once - it will spot the aggregates are repeated. Have a look at the execution plan.It will probablyy give the same as this - which might save typing in some cases.select TotalMyField = sumfld, MyOtherField = sumfld / cntfldfrom(SELECT sumfld = Sum(MyField), cntfld = Count(MyField)FROM MyTable WHERE MyField > 10) a==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Aggregate function in multiple parts of Select.