Using CASE WHEN statement

Suggested dev team to change the following SQL:

update t1 
set b=(SELECT    
               decode(count(*),0,'N','Y')
          FROM t2 
         WHERE t1.id = t2.id);

to the following structruce:

update t1  set b= case when  
                     exists ( select null from t2 where t1.id =t2.id  )
                       then 'Y' 
                       else 'N' end;

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

Combine Multiples Scans with CASE Statements

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation.

hr@ORCL> select count(*) from employees where salary < 10000; 
 COUNT(*)
----------        
87

hr@ORCL>  select sum(salary) from employees where salary < 10000;
SUM(SALARY)
-----------   
463795.5

hr@ORCL> select avg(salary) from employees where salary < 10000;
AVG(SALARY)
----------- 
5330.98276

hr@ORCL> SELECT COUNT (CASE WHEN salary < 10000  
2                     THEN 1 ELSE null END) count1,  
3               SUM (CASE WHEN salary < 10000  
4                     THEN salary ELSE null END) sum1,  
5               AVG (CASE WHEN salary < 10000  
6                     THEN salary ELSE null END) avg1  
7        FROM employees;    

COUNT1       SUM1       AVG1
---------- ---------- ----------        
      87   463795.5    5330.98276