4

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!

Prix
  • 19,417
  • 15
  • 73
  • 132
Jason
  • 51,583
  • 38
  • 133
  • 185
  • Can you provide specifics? Like some sample query you've created. – Paulo Santos May 20 '09 at 19:21
  • 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. – araqnid May 20 '09 at 19:48

4 Answers4

11

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)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • FANTASTIC, thank you. I didn't think to do COALESCE(subquery, 0). That's an awesome trick. – Jason May 20 '09 at 19:46
  • You could also put the COALESCE() inside each subquery, wrapping the SUM() or COUNT(). Six of one and half-dozen of the other. – Bill Karwin May 20 '09 at 20:38
3

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?

Goyuix
  • 23,614
  • 14
  • 84
  • 128
  • 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 – Jason May 20 '09 at 19:29
  • 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? – Jason May 20 '09 at 19:44
  • COALESCE(subquery2, 0) - means value of subquery2, or 0 if that's null. – araqnid May 20 '09 at 19:44
  • +1 for getting me on the right track, although Bill finished the job. – Jason May 20 '09 at 19:53
1

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.

AllenG
  • 8,112
  • 29
  • 40
0

It might be unnecessary to say, but since you're using it inside a program, You'd rather use program logic to sum the two results (NULL and a number), due to portability issues.

Who knows when COALESCE function is deprecated or if another DBMS supports it or not.

AbiusX
  • 2,379
  • 20
  • 26
  • 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... – Vitalij Mar 23 '19 at 11:41