SQL sum of 3 columns

I know this question might seem a little basic stuff but I want to make sure I get the right syntax because this line of code wi开发者_如何学JAVAll run often:

I want to sum columns A, B and C of table "alphabet" for all rows which have an id included in my IN clause.

This is how I would do it but I'd like a confirmation if possible:

SELECT SUM(A + B + C) as "subtotal" FROM alphabet WHERE id IN ('1','5','378');

If id is not a string, you shouldn't quote those values.

SELECT SUM(A + B + C) as "subtotal" FROM alphabet WHERE id IN (1,5,378);

This should work, but may may have one non-obvious consequence. If any row has A not null and one of the others null, that row will drop out of the summation because a null plus anything else is null, and nulls are ignored by the SUM operator. Thus it may be safer to write:

SELECT SUM(A) + SUM(B) + SUM(C) as "subtotal" FROM alphabet WHERE id IN (1,5,378);

This suffers from the same potential problem, but it is less likely to happen because an entire column would have to be null. There are various dialect specific ways to defend against that problem if you are still concerned. The most portable is the painfully verbose:

SELECT SUM(
      CASE
        WHEN A IS NULL
        THEN 0
        ELSE A
      END
      +
      CASE
        WHEN B IS NULL
        THEN 0
        ELSE B
      END
      +
      CASE
        WHEN c IS NULL
        THEN 0
        ELSE C
      END
    ) as "subtotal"
FROM alphabet
WHERE id IN (1,5,378);

"Run often" suggests that performance will be a concern. If that's true, make sure the the id has an index on it. If it's a primary key, it'll have an index by default.

If you don't have an index, you'll end up doing a table scan - examining each and every row. Performance will get worse as the size of the table grows.

Do an EXPLAIN PLAN on your query and make sure you don't see a TABLE SCAN anywhere.

declare 
    @a int = 0, 
    @b int = 5,
    @c int = NULL

select 
    ISNULL(@a, 0) + ISNULL(@b, 0) + ISNULL(@c, 0)