Geek Answers Handbook

Calculation column based on two calculation columns

I am trying to make a rather complicated SELECT calculation, which I will generalize:

  • The main query is a wildcard for a table
  • In one subquery, there are COUNT() all elements based on the condition (this works fine)
  • In another subquery, there is the SUM() number of numbers in the column based on another condition. This also works correctly, except when the records do not meet the conditions, returns NULL .

At first, I wanted to add two subqueries, something like (subquery1)+(subquery2) AS total , which works fine if subquery2 is not equal to zero, in which case total becomes zero, regardless of the result of the result of subquery1. My second thought was to try to create a third column that was supposed to calculate two subqueries (i.e. (subquery1) AS count1, (subquery2) AS count2, count1+count2 AS total ), but I don’t think it is possible compute two calculated columns, and even if that were the case, I feel similarly to the same problem.

Does anyone have an elegant solution to this problem beyond getting two subquery values ​​and summing them in my program?

Thanks!

+4
source share
4 answers

There are two questions here:

  • You cannot use one column alias in another expression in the same SELECT list.

    However, you can set aliases in a subquery of a view and use them in an external query.

  • You cannot do arithmetic with NULL because NULL is not equal to zero.

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

Here is an example:

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

(remember that the table alias must be assigned to the table, "t" in this example)

+11
source

First, the COALESCE function should help you take care of any zero issues.

Is it possible to use the union to combine these two queries into one result set, and then consider it as a subquery for further analysis?

Or maybe I did not quite understand your question?

+3
source

I would try (for the second query) something like: SELECT SUM (ISNULL (myColumn, 0)) // Please check the syntax of this before using it, though ...

This should return 0 instead of zero if any instance of this column is zero.

+1
source

There is no need to say, but since you use it inside the program, you will prefer to use program logic to sum the two results (NULL and number) due to portability problems.

Who knows when the COALESCE function is outdated or supports another DBMS or not.

0
source

More articles:


All Articles