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!

Solution 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)

Solution 2

First off, the COALESCE function should help you take care of any null problems.

Could you use a union to merge those two queries into a single result set, then treat it as a subquery for further analysis?

Or maybe I did not completely understand your question?

Solution 3

I would try (for the second query) something like: SELECT SUM(ISNULL(myColumn, 0)) //Please verify syntax on that before you use it, though...

This should return 0 instead of null for any instance of that column being zero.

Comments

[Paulo Santos]

Can you provide specifics? Like some sample query you've created.

[araqnid]

Also remember the COALESCE(SUM(CASE WHEN condition THEN 1 ELSE 0 END),0) trick to conditionally count items: you might be able to combine to two subqueries into just one, or roll them into the main query.

[Ricardo Gomes]

i'm not familiar with UNION or COALESCE, although i've seen them before... i think you're understanding correctly, as i am trying to merge them into a single result set. could i do ((subquery1)+COALESCE(subquery2)) ?... or what

[Ricardo Gomes]

awesome... COALESCE did the trick, although the SQL string is quite long and unwieldy since i have to repeat the SUM() subquery twice (now 3 subqueries as one column, oy) is there any way to do this without repeating a whole subquery again?

[araqnid]

COALESCE(subquery2, 0) - means value of subquery2, or 0 if that's null.

[Ricardo Gomes]

FANTASTIC, thank you. I didn't think to do COALESCE(subquery, 0). That's an awesome trick.

[Ricardo Gomes]

+1 for getting me on the right track, although Bill finished the job.

[Bill Karwin]

You could also put the COALESCE() inside each subquery, wrapping the SUM() or COUNT(). Six of one and half-dozen of the other.

[Vitalij]

And who knows, when program logic's function is deprecated or if another programming language supports it or not. P.S. 10 years later COALESCE still not deprecated...