Calculated Column Based on Two Calculated Columns

Question

I'm trying to do a rather complicated SELECT computation that I will generalize:

1. Main query is a wildcard select for a table
2. One subquery does a COUNT() of all items based on a condition (this works fine)
3. Another subquery does a SUM() of numbers in a column based on another condition. This also works correctly, except when no records meet the conditions, it returns NULL.

I initially wanted to add up the two subqueries, something like (subquery1)+(subquery2) AS total which works fine unless subquery2 is null, in which case total becomes null, regardless of what the result of subquery1 is. My second thought was to try to create a third column that was to be a calculation of the two subqueries (ie, (subquery1) AS count1, (subquery2) AS count2, count1+count2 AS total) but I don't think it's possible to calculate two calculated columns, and even if it were, I feel like the same problem applies.

Does anyone have an elegant solution to this problem outside of just getting the two subquery values and totalling them in my program?

Thanks!

Answer 1

Two issues going on here:

• You can't use one column alias in another expression in the same SELECT list.

  However, you can establish aliases in a derived table subquery and use them in an outer query.

• You can't do arithmetic with NULL, because NULL is not zero.

  However, you can "default" NULL to a non-NULL value using the COALESCE() function. This function returns its first non-NULL argument.

Here's an example:

SELECT *, count1+count2 AS total
FROM (SELECT *, COALESCE((subquery1), 0) AS count1, 
                COALESCE((subquery2), 0) AS count2 
      FROM ... ) t;

(remember that a derived table must be given a table alias, "t" in this example)