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