Oracle COUNT function

COUNT function returns the total number from the expression. It is an example of aggregate functions.
Syntax:
1. select count(column_name) from table_name;
2. select count(*) from table_name;Count(*) returns total number of rows.
Count(column_name) returns total number of rows have data. ie it ignores null columns.
Examples:-Consider below EMP table structure
Empno Empname Manager Deptno
10 Bill
5
11 Solomon Bill 5
12 Susan Bill 5
13 Wendy Solomon 1
14 Benjamin Solomon 1
15 Tom Solomon 1
16 Henry Solomon 2
17 Robert Susan 2
18 Paul Solomon 2
1. Find the number of employees.
select count(*) from EMP;    
COUNT(*)
—————-
   9
2. Find the total number of managers.
select count(manager) from EMP;     
COUNT(MANAGER)
—————-
   8
3. Find all managers handling more than one employees.
select manager ,count(*) from EMP group by manager having count(*) > 1;
MANAGER  COUNT(*)
—————— ———-
Bill                    2
Solomon           5
4. Find total number of managers.
select count(distinct manager) from EMP;
COUNT(DISTINCTMANAGER)
——————————————–
3
Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s