COUNT function in Oracle

COUNT is one of the vital Numeric/Math functions of Oracle. It is used to get the Count of an expression. The COUNT function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.

Syntax: To calculate simple count.

SELECT COUNT (aggregate_expression)
FROM tables
WHERE conditions;

Syntax 2: To calculate count and grouping the results by one or more columns.

SELECT expression1, expression2, ... expression_n,
COUNT(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Parameters: expression1, expression2, … expression_n: It is used to specify the expressions to be included in the GROUP BY clause but is not encapsulated in the COUNT function. aggregate_expression: It is used to specify the column or expression that will be counted. tables: It is used to specify the tables to retrieve the records from. conditions: It is an optional parameter which is used to specify the conditions that must be met for selection.

Example 1: Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        20
2	        Smiley	        19
3	        Happy	
4	        James	
5	        Bond

SELECT COUNT(student_id)
FROM students;

Output:

Explanation: The count of the total number of NOT NULL values of the student_id is 5 and so is the result.

Example 2: Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        20
2	        Smiley	        19
3	        Happy	
4	        James	
5	        Bond

SELECT COUNT(student_age)
FROM students;

Output: 3

Explanation: The count of the total number of NOT NULL values of the student_age is 3 and so is the result.

Example 3: Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        20
2	        Smiley	        19
3	        Happy	
4	        James	
5	        Bond

SELECT COUNT(student_id)
FROM students
WHERE student_id > 2;

Output:

Explanation: The count of the total number of NOT NULL values of the student_id greater than 2 is 3 and so is the result.

Example 4:

SELECT COUNT(*) AS "COUNT Students"
FROM students
WHERE student_id > 10;

Explanation: Here we will get the count of the total number of NOT NULL values of the student_id greater than 10.

Example 5:

SELECT COUNT(DISTINCT class) AS "COUNT Students"
FROM students
WHERE student_id > 10;

Explanation: Here we will get the count of the total number of NOT NULL values of the student_id greater than 10, but no two equal student_id will be counted twice, since we are using the DISTINCT clause.

Example 6:

SELECT class, COUNT(*) AS "COUNT Students"
FROM students
WHERE student_id > 10
GROUP BY class;

Explanation: Here we will get the name of the class and the count of the number of students in the associated class, since we are using the GROUP BY clause. The number of students will be counted only for the Not Null values and only for those students whose student_id is greater than 10.

Please follow and like us: