LAG & LEAD Analytic function in Oracle

The LAG and LEAD  and  other analytic functions are available in Oracle to  give access to multiple rows within a table, without the need for a self-join. These function will be very handy.
For example: 
To get previous record column value
To calculate difference between column value. 



http://oracle-base.com/articles/misc/lag-lead-analytic-functions.php

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm#i83834

http://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php

Using sql count/sum in a case statement in Oracle

Note :
For COUNT : need to use null  value you like to ignore
For SUM  : need to use null /zero value you like to ignore




select count(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;


select sum(case
             when retrieve_time is null then
              0
             else
              1
           END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;

 select sum(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30;



select to_char(CREATED_TIME, 'YYYY/MM/DD'),
       count(case
               when retrieve_time is null then
                null
               else
                1
             END) as retrieved
  from TABLE_A
 where CREATED_TIME > gmt_sysdate - 30
 group by to_char(CREATED_TIME, 'YYYY/MM/DD');