What is the use of COUNT with DISTINCT option?

COUNT is a group function. That is which gives a result by summarizing multiple rows. All group 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 this combined with COUNT is used to count only unique mentioned value.

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

Select * from exforsys;

Gives output as below:


----- ------- ------ ---

1000 SRI 10000 12-MAR-1978

2000 SRI 50000 13-JUN-1980

3000 SRI 60000 23-APR-1998

4000 JOHN 5000 21-MAR-1981

The query with count with DISTINCT option which is given below namely:

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

Gives output as


---------------------- -------------- --------

2                          4              4

Thus in the above we find that COUNT(EMPNAME) gives 4 that is it counts all records of empname but COUNT(DISTINCTEMPNAME) gives output as 2 because there are three records having empname as SRI and it is taken only once and one empname JOHN is counted which gives result as 2.

Questions by GeekAdmin   answers by GeekAdmin


This Question is not yet answered!


Related Answered Questions


Related Open Questions