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.
SELECT COUNT(expression) FROM tables WHERE predicates;
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>
- 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