Friday, February 27, 2009

Using Case When

Considering the following table, suppose I want to count how many values associated with the same key are greater than 10, what can I do?


SQL> select * from t;

KEY VAL
---------- ----------
1 1
1 2
1 10
1 11
2 1
2 2
2 3
3 13
3 33
3 3

10 rows selected.


If I use the following statement, the problem is it does not show key=2, in which case there are no values greater than 10:


SQL> select key, count(*) from t where val > 10 group by key;

KEY COUNT(*)
---------- ----------
1 1
3 2


 

Case when probably is what I want:


SQL> select key, sum(case when val > 10 then 1 else 0 end) from t group by key;

KEY SUM(CASEWHENVAL>10THEN1ELSE0END)
---------- --------------------------------
1 1
2 0
3 2

No comments: