SQL 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


Related topics
AVG function
MAX Function

No comments:

Post a Comment