SQL COUNT Function is used to return number of rows fetched by a query. Oracle COUNT Function can be used to count number of rows that matches criteria set in SQL WHERE Clause.Oracle COUNT Function can be used as SQL SELECT COUNT, SQL COUNT DISTINCT, SQL COUNT Group By Statements.
SQL COUNT Function Syntax
SELECT COUNT(expression) FROM table_name WHERE conditions;
SQL COUNT Function Examples
Suppose we have a table named “Employee” with the data as shown below.
Oracle COUNT Function application in different scenarios.
SQL COUNT Function – SQL SELECT COUNT (SQL COUNT Rows) Example
SQL COUNT Function can be used on single column of table as SQL SELECT COUNT Statement.
For example, SQL SELECT COUNT query below will count number of employees from ’employee’ table.
SELECT COUNT(employee_name) FROM employee;
SQL SELECT COUNT query above returns ‘5’ as there are 5 employees in ’employee’ table.
SQL COUNT Function – SQL WHERE Clause Example
Oracle COUNT Function can be used with SQL WHERE Clause.
For example, suppose we wish to view number of employees getting salary more than ‘21000’.
Oracle COUNT Function query below will fetch the number of employees having salary more than ‘21000’.
SELECT COUNT(*) FROM employee WHERE salary > 21000;
Oracle COUNT Function query above returns count of employees earning more than ‘21000’, which in our case is 3.
SQL COUNT Function – SQL COUNT DISTINCT Example
SQL COUNT DISTINCT query is use to view count of unique result.
For this we have to combine the Oracle COUNT Function with the SQL DISTINCT Clause.
For example, SQL COUNT DISTINCT query below will return the number of unique departments.
SELECT COUNT(DISTINCT(department)) FROM employee;
SQL COUNT DISTINCT query above returns ‘4’ as number of unique departments is ‘4’.
The department ‘Sales’ occurs twice in employee table and hence been counted only once.
SQL COUNT(*) Function – SQL COUNT GROUP BY Example
SQL COUNT Group By query is used to count records group wise.
For this we have to combine Oracle COUNT Function with the SQL GROUP BY Clause.
For example, the SQL COUNT Group By query below will return the department name and number of employees in each department.
SELECT department ,COUNT(*) FROM employee GROUP BY department;
SQL COUNT Group By query above returns the following data: