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;

—– ——- —— —
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;

———————- ————– ——–
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 .

Editorial Team at Geekinterview is a team of HR and Career Advice members led by Chandra Vennapoosa.

Editorial Team – who has written posts on Online Learning.

Pin It