-1

Access Code

iif(Format(SUM(Field))='',0,SUM(Field))

SQL code

IsNull(SUM(Field),0) As Amt

In the Access Code statement it is clearly shown that the query has to run SUM function 2 times to get SUM and 0 if records are blank.

But I want to know that internally how many times SQL is running SUM function in the SQL statement? 1 or 2? Please provide some explanation.

Agent_Spock
  • 1,107
  • 2
  • 16
  • 44
  • 2
    Once (as [opposed to `COALESCE`](http://sqlmag.com/t-sql/coalesce-vs-isnull) btw, search "Used with Subqueries"). – Tim Schmelter Oct 06 '14 at 12:35
  • MySQL **or** MS SQL **or** both ? Normally, if you have `null` values, you'd use it like: `SUM(IFNULL(field, 0))` - because it doesn't automatically cast `null` to `0` (-MySQL). – Daniel W. Oct 06 '14 at 12:36
  • sorry question only SQL – Agent_Spock Oct 06 '14 at 12:38
  • It's tagged `sql-server` and no mention of `mysql` at all... I would assume he's asking about `sql-server`.. – Siyual Oct 06 '14 at 12:42
  • SQL is a language where you're meant to tell the system "what you want", not "how to do it" - so unless there's an actual performance issue with the query (and if there is, you ought to actually find out where the performance cost is - the system can tell you by generating query plans), just write the most straightforward code that expresses what you need. – Damien_The_Unbeliever Oct 06 '14 at 12:48
  • @TimSchmelter you should have added your comment as an answer because that is what i was looking for. Thanks – Agent_Spock Oct 06 '14 at 12:49

1 Answers1

1

My copy-pasted comment:

Once, as opposed to COALESCE:

Used with Subqueries

The ISNULL function has an important advantage over COALESCE in that internally it doesn't evaluate an input expression more than once. In accordance with standard SQL, COALESCE(v1, v2) is simply internally translated to CASE WHEN v1 IS NOT NULL THEN v1 ELSE v2 END. As a result, SQL Server might evaluate the expression v1 more than once, which can lead to all kinds of surprising results.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939