COUNT Function Examples | Teradata Vantage - Examples: Using the COUNT Function - Advanced SQL Engine - Teradata Database

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;

Note that without the GROUP BY clause, the select list cannot include the DeptNo column because it 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 them 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 ran 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 would have only registered non-null occurrences of DeptNo and would not have included 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).

Recall that in addition to the 21 employees in the Employee table who are assigned to a department, there are two new employees who 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(sex) 
   FROM Employee 
   WHERE sex = 'M' ;

The result is as follows.

   Count(Sex)
   ----------
           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 ;

In this case, 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