SQL: Computing sum of all values *and* a sum of only values matching condition

Question

Suppose I fetch a set of rows from several tables. I want to know the total sum of values in column x in these rows, as well as sum of only those values in x where the row satisfies some additional condition.

For example, let's say I fetched:

 X   Y
 1   0
10   0
20 100
35 100

I want to have a sum of all x (66) and x in those rows where x > y (11). So, I'd need something like:

SELECT sum(x) all_x, sum(x /* but only where x > y */) some_x FROM ...

Is there a way to formulate that in SQL? (Note that the condition is not a separate column in some table, I cannot group over it, or at least don't know how to do that.)

EDIT: I use Oracle Database, so depending on Oracle extensions is OK.

Answer 1

You could use a case expression inside the sum:

SELECT SUM (x) all_x, 
       SUM (CASE WHEN x > y THEN x ELSE 0 END) some_x 
FROM   my_table

Answer 2

You're looking for the CASE operator :

SELECT sum(X) all_x, 
       sum(CASE WHEN X>Y THEN X ELSE 0 END) some_x 
FROM Table1

In this case (no pun) you would get 11 for some_x You can use whatever condition you want instead of X>Y after the WHEN, and select whatever value instead of X.

SQL fiddle to test this query.