2

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.

3 Answers3

3

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thanks, I guessed it was something simple. Will accept once Stackoverflow allows me. –  Nov 07 '14 at 10:37
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.

P. Camilleri
  • 12,664
  • 7
  • 41
  • 76
2

Below Query will give What you want 

select SUM(x) as x,(select SUM(x) from test5 where x>y )as 'X>Y'
from test5
Sandesh
  • 109
  • 7
  • Not in Oracle. Sql Server yes, other databases maybe, but Oracle will think you're trying to group on the second column without specifying a `GROUP BY` clause, and the query will fall over. – Bacs Nov 07 '14 at 11:19