Preferred syntax for adding SQL SUMs together

Question

I want to return the total sales. That is to say the UnitPrice + TaxAmount for the entire set of data. I can write the SQL query two different ways with the same result returned.

SELECT SUM(UnitPrice + TaxAmount) AS 'TotalSales' FROM Sales

or

SELECT SUM(UnitPrice) + SUM(TaxAmount) AS 'TotalSales' FROM Sales

Is one of these queries perferrable over the other (performance or otherwise)? Or is this just a matter of taste?

Answer 1

I don't know performance-wise, but I would expect SUM(UnitPrice + TaxAmount) is probably fractionally better there.

From a readibility standpoint, I believe the first is preferable.

Answer 2

I would go with the first query, that way if you ever go from having an already calc'ed TaxAmount to needing to multiply the UnitPrice by a TaxRate your query won't change much. I would also think the performance would be better with the first query.

Answer 3

Use:

SELECT SUM(UnitPrice) + SUM(TaxAmount) AS 'TotalSales' 
  FROM Sales

...because using SUM(UnitPrice + TaxAmount) can return NULL if more than one of the values is NULL. You could wrap them in COALESCE:

SELECT SUM(COALESCE(UnitPrice, 0) + COALESCE(TaxAmount, 0))

Update

I don't see a performance difference between the two methods, and seeing that SUM() + SUM() is more NULL safe just seals the deal to me.

Answer 4

The first one will perform better (in SQL Server anyway), you want to do the fewest number of SUMs possible as it's a relatively expensive operation.

e: I realize nulls can impact this but given that the question states

I can write the SQL query two different ways with the same result returned.

I am assuming he does not have nulls to contend with.

e. Despite my stalker's assertions in the comments below you can easily verify the performance difference for yourself using sql trace.

Answer 5

Since you didn't specify a DB I don't know who can say which performs better. That said if I were a DB Engine writer I would probably try to get them to be pretty close in perf, but you should test and see.

As for readability I much feel the same as OMG Ponies SUM(UnitPrice) + SUM(TaxAmount) AS 'TotalSales'