SQL COUNT Function

SQL COUNT FunctionSQL 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.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 Marketing 15
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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:

DEPARTMENT COUNT(*)
Sales 2
IT 1
Marketing 1
Support 1

Leave a Reply

Your email address will not be published. Required fields are marked *