Tech Honey

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.

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

---