Be careful what you count for and you might get it.

Count is one of very important functions used in any database. But really we dont know that is the result count we get from COUNT function might be different based on how it is used. Let us consider the example of count and understand how there is some difference in usage.
if you count with count(‘x):
SQL> select count (‘x’) from departments;
COUNT(‘X’)
———-
27
If you count empty strings or NULLs:
SQL> select count (”) from departments;
COUNT(”)
———-
0
Again try with space then the result
SQL> select count (‘ ‘) from departments;
COUNT(”)
———-
27
But if the count is hidden in a subqry and if the count is disquised with bind var then:
SQL> variable x varchar2(10);
SQL> exec 😡 := ‘shahid’;
PL/SQL procedure successfully completed.
SQL> select 😡 as bindvar, d.*
2  from departments d
3  where ( select count (:x)
4  from employees e
5  where e.department_id = d.department_id
6  ) > 0;
BINDVAR   DEPARTMENT_ID DEPARTMENT_NAME      MANAGER_ID LOCATION_ID
——————————– ————- —————————— ———- ———–
shahid                       10               Administration                          200        1700
shahid                        20              Marketing                                 201        1800
shahid                        30              Purchasing                                114        1700
shahid                        40              Human Resources                     203        2400
shahid                        50              Shipping                                   121        1500
shahid                        60              IT                                            103        1400
shahid                        70              Public Relations                        204        2700
shahid                        80              Sales                                        145        2500
shahid                        90              Executive                                 100        1700
shahid                        100            Finance                                    108        1700
shahid                        110            Accounting                               205        1700

11 rows selected.
If you count with Null then:
SQL> exec 😡 := ”;
PL/SQL procedure successfully completed.
SQL> select 😡 as bindvar, d.*
2  from departments d
3  where ( select count (:x)
4  from employees e
5  where e.department_id = d.department_id
6  ) > 0 ;
no rows selected

SQL> select count(*), count(last_name), count(1) from employees;
COUNT(*) COUNT(LAST_NAME)   COUNT(1)
———- —————- ———-
107              107        107
SQL> select count (*), count(‘x’), count(1) from employees;
COUNT(*) COUNT(‘X’)   COUNT(1)
———- ———- ———-
107        107        107
Here in the above example:
Count(*) counts all occurrences including nulls. This is a row count for a given table.
Count(last_name) counts all non null occurrences of values in last_name. If ‘last_name’ has null values, count(last_name) will be less than count(*).
Count(1) counts all null values in a “pseudo column” created by the constant. Since the constant 1 will never be null, this would be similar to count(*) that is nothing but row count.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: