CODEWITHSUNDEEP
sqlsql-serversql-server-2008
nec tso
nec tso

Reputation: 669

Adding up subqueries with main query

I would like to know how can I add up the result of my subqueries within a table. For example, I want to add up the following two queries in one statement.

SELECT SUM(A) - (SELECT SUM(B) FROM MyTable WHERE C<A))
FROM MyTable WHERE B>C;

SELECT SUM(B) - (SELECT SUM(C) FROM MyTable WHERE D<F))
FROM MyTable WHERE B=C;

Upvotes: 1

Views: 64

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can literally just add them together:

SELECT (SELECT SUM(A) - (SELECT SUM(B) FROM MyTable WHERE C<A))
                         FROM MyTable WHERE B>C)  
        +
        (SELECT SUM(B) - (SELECT SUM(C) FROM MyTable WHERE D<F))
                          FROM MyTable WHERE B=C)

Same as:

SELECT 5 + 3

You could also probably use some CASE statements to do this without the subqueries, or to make it cleaner at the least, something like:

SELECT SUM(CASE WHEN B > C THEN A ELSE 0 END) - SUM(CASE WHEN C < A THEN B ELSE 0 END)
       +
       SUM(CASE WHEN B = C THEN B ELSE 0 END) - SUM(CASE WHEN D < F THEN C ELSE 0 END)
FROM MyTable

Upvotes: 3

Related Questions