COUNT() Function in SQL ,Using COUNT(*) and COUNT(1)

The COUNT() function returns the number of records in a tables based on sql query.
The COUNT function will only count those records in which the field in the brackets is NOT NULL.

SYNTAX:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

EXAMPLE:
STEP 1:Create the following table,’MYTABLE’ and insert few records.

SQL>  create table MYTABLE (NAME varchar2(50), Age number,id number);
SQL> Table created
SQL> insert into MYTABLE values('BBB',12,1);
SQL> insert into MYTABLE values('DDD',22,2);
SQL> insert into MYTABLE values('CCC',32,3);
SQL> insert into MYTABLE values('AAA',62,4);
SQL> insert into MYTABLE values('CCC',42,5);
SQL>5 rows inserted

STEP 2:Now run the following query.

SQL>  select count(*) from MYTABLE;

COUNT(*)
   5

COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters
If you use COUNT(*) the query will retrieve all fields from the table inorder to calculate count,To avoid this we can use COUNT(1) instead of COUNT(*).It will merely retrieve the numeric value of 1 for each record that meets your criteria.This can be used as a performance tip.

It is possible to use DISTINCT clause within the COUNT function to give exact count omitting duplicates.

SQL> select count(distinct name) names from mytable;
NAMES
  4
SQL>

You can also use GROUP BY function along with COUNT( Check GROUP BY Here).
This is required when you need a combination of coulmns and number of records in a result set.Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause.

EXAMPLE: The following query will give you name and number of occurances from MYTABLE.

SQL>  select name ,COUNT( NAME) from MYTABLE GROUP BY name;

NAME                                               COUNT(NAME)
BBB                                                       1
CCC                                                       2
AAA                                                       1
DDD                                                       1

SQL>

EXAMPLE: The following query will return people with age<40

SQL> select name ,count(*)NUMBER_OF_PEOPLE from mytable where age<40;
NAME                                               NUMBER_OF_PEOPLE
BBB                                                 1
CCC                                                 1
DDD                                                 1

SQL>

EXAMPLE: The following query will return people with age>30

SQL> select name ,count(*)NUMBER_OF_PEOPLE from mytable where age >30 group by name;
NAME                                               NUMBER_OF_PEOPLE
CCC                                                           2
AAA                                                           1
SQL>

Related Articles,

• SQL MIN Function | MIN() function in SQL
• SQL COUNT() Function ,Usage of COUNT(*) and COUNT(1)
• SQL order by ,using ‘ORDER BY’ clause in SQL
• Oracle Max(Date)? | sql max date
• Oracle Trim Function | PL/SQL TRIM Function.
• Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values
• SQL: MAX Function | SQL Max examples
• SQL GROUP BY Statement | SQL GROUP BY Clause examples
• NVL | Oracle/PLSQL: NVL Function

Technorati Tags:
SQL COUNT() Function, COUNT(*), COUNT(1), SQL COUNT