What is the use of COUNT with DISTINCT option

COUNT is a group function returns result by summarizing multiple rows. All group by value function will have the usage of DISTINCT or ALL option and so is the COUNT which uses the DISTINCT option. DISTINCT option is used to enforce uniqueness and  if combined with COUNT is used to count only unique mentioned value.

To understand this let us see an example. Consider exforsys table which has columns as empno, empname, salary, DOJ

Select * from exforsys;

EMPNO EMPNAME SALARY DOJ ----- ------- ------ --- 1000 SRI 10000 12-MAR-1978 2000 SRI 50000 13-JUN-1980 3000 SRI 60000 23-APR-1998 4000 JOHN 5000 21-MAR-1981

Select COUNT(DISTINCT empname),COUNT(empname),COUNT(*) from exforsys;

COUNT(DISTINCTEMPNAME) COUNT(EMPNAME) COUNT(*) ---------------------- -------------- -------- 2                          4              4

In the above sample, COUNT(EMPNAME) returned 4 that is it counts all records of empname but COUNT(DISTINCTEMPNAME) returned output 2 because there are three records with empname SRI .