Oracle
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 .
|
COUNT is a group function returns result by summarizing multiple rows. DISTINCT option is used to enforce uniqueness |
| its great combination of uniqe and group, in one group possibilities of duplicate values to avoide the wrong count this is the best solution |
|
To put it into simple words: When you want to Count a number of unique values in a set, you use COUNT with DISTINCT. Let's say: I have a table called BAG which is like this: ARTICLE TYPE HB Pencil Pencil Black Pen Pen White Eraser Eraser 2B Pencil Pencil Blue Pen Pen Now if I want to count the no. of items, I will write COUNT(ARTICLE) or COUNT(TYPE), which will give us '5'. But if we want to see how many types of articles we have but not the exact count. We will write COUNT(DISTINCT TYPE) which will give us 3. |
| Hey guys remember distinct is faster than group |
