COUNT Function Examples | VantageCloud Lake - Examples: Using the COUNT Function - Teradata Vantage

Example: Reporting the Number of Employees in Each Department

COUNT(*) reports the number of employees in each department because the GROUP BY clause groups results by department number.

   SELECT DeptNo, COUNT(*) FROM Employee 
   GROUP BY DeptNo 
   ORDER BY DeptNo;

Without the GROUP BY clause, only the total number of employees represented in the Employee table is reported:

   SELECT COUNT(*) FROM Employee;

Without the GROUP BY clause, the select list cannot include the DeptNo column because that column returns any number of values and COUNT(*) returns only one value.

Example: Employees Returned as Nulls

If any employees have been inserted but not yet assigned to a department, the return includes those employees as nulls in the DeptNo column.

   SELECT DeptNo, COUNT(*) FROM Employee 
   GROUP BY DeptNo 
   ORDER BY DeptNo;

Assuming that two new employees are unassigned, the results table is:

   DeptNo   Count(*)
   ------   --------
   ?               2
      100          4
      300          3
      500          7
      600          4
      700          3

Example: Counting Employees Not Yet Assigned to a Department

If you run the report in Example: Reporting the Number of Employees in Each Department using SELECT... COUNT … without grouping the results by department number, the results table has only registered non-null occurrences of DeptNo and does not include the two employees not yet assigned to a department(nulls). The counts differ (23 in Example: Reporting the Number of Employees in Each Department as opposed to 21 using the statement documented in this example).

Twenty-one employees in the Employee table are assigned to a department, and two new employees are not yet assigned to a department (the row for each new employee has a null department number).

   SELECT COUNT(deptno) FROM employee ; 

The result of this SELECT is that COUNT returns a total of the non-null occurrences of department number.

Because aggregate functions ignore nulls, the two new employees are not reflected in the figure.

   Count(DeptNo) 
   --------------
              21 

Example: Using COUNT to Find the Number of Employees by Gender

This example uses COUNT to provide the number of male employees in the Employee table of the database.

   SELECT COUNT(gender) 
   FROM Employee 
   WHERE gender = 'M' ;

The result is as follows.

   Count(gender)
   ----------
           12

Example: Providing a Total of the Rows with Non-Null Department Numbers

In this example COUNT provides, for each department, a total of the rows that have non-null department numbers.

   SELECT deptno, COUNT(deptno) 
   FROM employee 
   GROUP BY deptno 
   ORDER BY deptno ;

Notice once again that the two new employees are not included in the count.

   DeptNo   Count(DeptNo)
   ------   -------------
      100               4
      300               3
      500               7
      600               4
      700               3

Example: Returning the Number of Employees by Department

To get the number of employees by department, use COUNT(*) with GROUP BY and ORDER BY clauses.

   SELECT deptno, COUNT(*) 
   FROM employee 
   GROUP BY deptno 
   ORDER BY deptno ;

The nulls are included, indicated by QUESTION MARK.

   DeptNo   Count(*)
   ------   --------
   ?               2
      100          4
      300          3
      500          7
      600          4
      700          3

Example: Determining the Number of Departments in the Employee Table

To determine the number of departments in the Employee table, use COUNT (DISTINCT) as illustrated in the following SELECT COUNT.

   SELECT COUNT (DISTINCT DeptNo) 
   FROM Employee ;

The system responds with the following report.

   Count(Distinct(DeptNo))
   -----------------------
                         5