Calculated Column Based on Two Calculated Columns

4
votes

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!

4
mysqlsubquerycalculated-columns
Can you provide specifics? Like some sample query you've created. – Paulo Santos
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

4 Answers

11
votes

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)

3
votes

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?

1
votes

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.

0
votes

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.