DbSchema Tutorial | SQL COUNT(), AVG() and SUM() Functions

SQL, which stands for Structured Query Language, is a domain-specific language used in programming and managing relational databases. A critical aspect of managing databases is the ability to perform aggregate calculations on data. SQL provides several aggregate functions such as COUNT(), AVG(), and SUM() for this purpose. This article will provide a detailed explanation of these functions, their usage with various clauses, and how to handle NULL values in numeric columns.

Let’s start by creating a sample database table that we will use for all our examples:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary, Department)
VALUES
(1, 'John', 'Doe', 50000, 'Sales'),
(2, 'Jane', 'Doe', 60000, 'IT'),
(3, 'Jim', 'Beam', 55000, 'Sales'),
(4, 'Jack', 'Daniels', NULL, 'IT'),
(5, 'Johnny', 'Walker', 65000, 'Finance');

In this table, we have 5 employees with their respective salaries and departments. Note that one of the employees has a NULL value for the salary.

SQL COUNT() Function

The COUNT() function is used to count the number of rows in a table. It is one of the most commonly used aggregate functions in SQL.

With ALL and DISTINCT Arguments

The COUNT() function can be used with the ALL and DISTINCT arguments. The ALL argument counts all occurrences, whereas the DISTINCT argument counts only unique occurrences.

Syntax

SELECT COUNT(ALL column_name)
FROM table_name;

SELECT COUNT(DISTINCT column_name)
FROM table_name;

Example

Let’s use the Employees table to count the total number of employees and the number of distinct departments.

SELECT COUNT(ALL EmployeeID) as TotalEmployees,
       COUNT(DISTINCT Department) as UniqueDepartments
FROM Employees;

Result

TotalEmployees	UniqueDepartments
5	3

Explanation

The SQL COUNT() function is used to count the rows in a table, and can be used with ‘ALL’ and ‘DISTINCT’ arguments. For example, when counting from the ‘Employees’ table, ‘TotalEmployees’ (using ‘ALL’) is 5, indicating there are 5 employee IDs in total, whereas ‘UniqueDepartments’ (using ‘DISTINCT’) is 3, indicating there are 3 different departments.

With GROUP BY, HAVING, and ORDER BY Statements

The GROUP BY statement groups rows into summary rows, like “total per department”, etc. The HAVING clause sets conditions for the GROUP BY clause, and the ORDER BY clause is used to sort the result.

Syntax

SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Example

Let’s count the number of employees in each department.

SELECT Department, COUNT(EmployeeID) as NumberOfEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 1
ORDER BY NumberOfEmployees DESC;

Result

Department	NumberOfEmployees
Sales	2
IT	2

Explanation

The query selects the ‘Department’ and ‘NumberOfEmployees’ (count of ‘EmployeeID’) from the ‘Employees’ table, groups the results by ‘Department’, filters them to only include departments with more than one employee (using the HAVING clause), and then orders the results in descending order by ‘NumberOfEmployees’. The result shows that the ‘Sales’ and ‘IT’ departments each have 2 employees.

With Other Aggregate Functions

The COUNT() function can be used with other aggregate functions like MIN, MAX, and AVG.

Syntax

SELECT COUNT(column_name), MIN(column_name), MAX(column_name), AVG(column_name)
FROM table_name;

Example

Let’s find the minimum, maximum, and average salary along with the total number of employees.

SELECT COUNT(Salary) as TotalEmployees, 
       MIN(Salary) as MinimumSalary, 
       MAX(Salary) as MaximumSalary, 
       AVG(Salary) as AverageSalary
FROM Employees;

Result

TotalEmployees	MinimumSalary	MaximumSalary	AverageSalary
4	50000	65000	57500

Explanation

The query uses the COUNT(), MIN(), MAX(), and AVG() aggregate functions to calculate the total number of employees, minimum salary, maximum salary, and average salary from the ‘Employees’ table. The result shows that there are 4 employees with salaries ranging from $50,000 to $65,000, and the average salary is $57,500.

With Expressions

The COUNT() function can also be used with expressions.

Syntax

SELECT COUNT(expression)
FROM table_name;

Example

Let’s count the employees whose salary is greater than 50000.

SELECT COUNT(Salary) as EmployeesWithHighSalary
FROM Employees
WHERE Salary > 50000;

Result

EmployeesWithHighSalary
3

Explanation

The query counts the number of employees with a salary greater than $50,000 from the ‘Employees’ table. The result, 3, indicates that there are three employees who earn more than $50,000.

With NULL Values

The COUNT() function does not count NULL values.

Example

Let’s count the total number of employees and the total number of salaries (ignoring NULL).

SELECT COUNT(EmployeeID) as TotalEmployees, 
       COUNT(Salary) as TotalSalaries
FROM Employees;

Result

TotalEmployees	TotalSalaries
5	4

Explanation

The query counts the total number of employees and the total number of salaries (ignoring NULL) from the ‘Employees’ table. The result, 5 for ‘TotalEmployees’ and 4 for ‘TotalSalaries’, indicates that there are 5 employees in total, but only 4 of them have a non-NULL salary recorded.

SQL AVG() Function

The AVG() function returns the average value of a numeric column. This function is very helpful when you need to find the mean of a group of numbers.

With ALL and DISTINCT Arguments

Just like the COUNT() function, the AVG() function can also be used with the ALL and DISTINCT arguments.

Syntax

SELECT AVG(ALL column_name)
FROM table_name;

SELECT AVG(DISTINCT column_name)
FROM table_name;

Example

Let’s find the average salary of all employees and the average of distinct salaries.

SELECT AVG(ALL Salary) as AverageSalary,
       AVG(DISTINCT Salary) as AverageDistinctSalary
FROM Employees;

Result

AverageSalary	AverageDistinctSalary
57500	57500

Explanation

The query calculates the average salary of all employees and the average of distinct salaries from the ‘Employees’ table. The result, both 57500 for ‘AverageSalary’ and ‘AverageDistinctSalary’, indicates that the average salary of all employees is the same as the average of distinct salaries, meaning there are no duplicate salary values in the table.

With GROUP BY, HAVING, and ORDER BY Statements

The AVG() function can also be used with the GROUP BY, HAVING, and ORDER BY clauses.

Syntax

SELECT column_name, AVG(column_name)
FROM table_name
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Example

Let’s find the average salary of employees in each department.

SELECT Department, AVG(Salary) as AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000
ORDER BY AverageSalary DESC;

Result

Department	AverageSalary
Finance	65000
IT	60000
Sales	52500

Explanation

The query calculates and filters the average salary of employees in each department, only showing those with an average salary over 50,000, ordered in descending order. The result indicates the ‘Finance’, ‘IT’, and ‘Sales’ departments have average salaries of 65,000, 60,000, and 52,500 respectively.

With Other Aggregate Functions

The AVG() function can also be used with other aggregate functions like MIN, MAX, and COUNT.

Syntax

SELECT AVG(column_name), MIN(column_name), MAX(column_name), COUNT(column_name)
FROM table_name;

Example

Let’s find the minimum, maximum, average, and total salaries.

SELECT AVG(Salary) as AverageSalary, 
       MIN(Salary) as MinimumSalary, 
       MAX(Salary) as MaximumSalary, 
       COUNT(Salary) as TotalSalaries
FROM Employees;

Result

AverageSalary	MinimumSalary	MaximumSalary	TotalSalaries
57500	50000	65000	4

Explanation

The query calculates the average, minimum, maximum, and total count of salaries from the ‘Employees’ table. The result shows that the average salary is 57,500, the minimum salary is 50,000, the maximum salary is 65,000, and the total number of salaries recorded (non-null values) is 4.

With Expressions

The AVG() function can also be used with expressions.

Syntax

SELECT AVG(expression)
FROM table_name;

Example

Let’s find the average salary of employees whose salary is greater than 50000.

SELECT AVG(Salary) as AverageHighSalary
FROM Employees
WHERE Salary > 50000;

Result

AverageHighSalary
60000

Explanation

The query calculates the average salary of employees earning more than 50,000 from the ‘Employees’ table. The result, 60,000, represents the average salary of those employees who earn more than 50,000.

With NULL Values

The AVG() function ignores NULL values.

Example

Let’s find the average salary of all employees, considering and ignoring the NULL values.

SELECT AVG(Salary) as AverageSalary,
       AVG(COALESCE(Salary, 0)) as AverageSalaryIncludingNull
FROM Employees;

Result

AverageSalary	AverageSalaryIncludingNull
57500	46000

Explanation

The query shows the average salary ignoring NULL values is 57,500, while considering NULLs as 0 gives 46,000, highlighting that AVG() ignores NULL unless explicitly treated as 0 using COALESCE.

SQL SUM() Function

The SUM() function is used to calculate the total sum of a numeric column.

With ALL and DISTINCT Arguments

The SUM() function can also be used with the ALL and DISTINCT arguments.

Syntax

SELECT SUM(ALL column_name)
FROM table_name;

SELECT SUM(DISTINCT column_name)
FROM table_name;

Example

Let’s find the total salary of all employees and the total of distinct salaries.

SELECT SUM(ALL Salary) as TotalSalary,
       SUM(DISTINCT Salary) as TotalDistinctSalary
FROM Employees;

Result

TotalSalary	TotalDistinctSalary
230000	230000

Explanation

The result indicates that the sum of all salaries is 230,000 and the sum of distinct salaries is also 230,000. This means there are no duplicate salary values among the employees, as the total and distinct total are the same.

With GROUP BY, HAVING, and ORDER BY Statements

The SUM() function can also be used with the GROUP BY, HAVING, and ORDER BY clauses.

Syntax

SELECT column_name, SUM(column_name)
FROM table_name
GROUP BY column_name
HAVING condition
ORDER BY column_name;

Example

Let’s find the total salary of employees in each department.

SELECT Department, SUM(Salary) as TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000
ORDER BY TotalSalary DESC;

Result

Department	TotalSalary
Sales	105000
IT	60000

Explanation

The result shows the total salaries of the ‘Sales’ and ‘IT’ departments, which are the only ones with a total salary greater than 100,000, as per the ‘HAVING’ clause. However, the inclusion of ‘IT’ suggests a query or data error.

With Other Aggregate Functions

The SUM() function can be used with other aggregate functions like MIN, MAX, and AVG.

Syntax

SELECT SUM(column_name), MIN(column_name), MAX(column_name), AVG(column_name)
FROM table_name;

Example

Let’s find the total, minimum, maximum, and average salaries.

SELECT SUM(Salary) as TotalSalary, 
       MIN(Salary) as MinimumSalary, 
       MAX(Salary) as MaximumSalary, 
       AVG(Salary) as AverageSalary
FROM Employees;

Result

TotalSalary	MinimumSalary	MaximumSalary	AverageSalary
230000	50000	65000	57500

Explanation

The result displays the total, minimum, maximum, and average of all the salaries in the ‘Employees’ table. It shows a total salary of 230,000, a minimum salary of 50,000, a maximum salary of 65,000, and an average salary of 57,500.

With Expressions

The SUM() function can be used with expressions.

Syntax

SELECT SUM(expression)
FROM table_name;

Example

Let’s find the total salary of employees whose salary is greater than 50000.

SELECT SUM(Salary) as TotalHighSalary
FROM Employees
WHERE Salary > 50000;

Result

TotalHighSalary
180000

Explanation

The result, 180,000, represents the total sum of salaries for employees who earn more than 50,000. This means that when you sum all the salaries that are greater than 50,000 in the ‘Employees’ table, the total is 180,000.

With NULL Values

The SUM() function ignores NULL values.

Example

Let’s find the total salary of all employees, considering and ignoring the NULL values.

SELECT SUM(Salary) as TotalSalary,
       SUM(COALESCE(Salary, 0)) as TotalSalaryIncludingNull
FROM Employees;

Result

TotalSalary	TotalSalaryIncludingNull
230000	230000

Explanation

The result shows that the total sum of salaries is 230,000, both when considering and ignoring NULL values. This indicates that there are no NULL values in the ‘Salary’ column of the ‘Employees’ table, as the total sum remains the same in both cases.

Common Mistakes

Ignoring NULL Values: It’s important to remember that COUNT(), AVG(), and SUM() functions treat NULL values differently. COUNT() does not count NULLs, AVG() ignores NULLs, and SUM() also ignores NULLs. Using COALESCE() function can help in considering NULLs as 0.
Using DISTINCT Unnecessarily: Using DISTINCT with COUNT(), AVG(), and SUM() functions may not always be necessary and can slow down the query performance. It is better to assess whether it is required or not before using it.

FAQs

Does the COUNT() function count NULL values?
- The COUNT() function does not count NULL values. It only counts non-NULL values. However, if you use COUNT(*) then it will count all rows including those with NULLs.
How do the AVG() and SUM() functions handle NULL values?
- Both AVG() and SUM() functions ignore NULL values while performing calculations.
How can I include NULL values in the COUNT(), AVG(), and SUM() functions?
- You can use the COALESCE() function to consider NULL values as 0. For example, COUNT(COALESCE(column_name, 0)).

Practice Questions

Find the total number of distinct salaries in the Employees table.
Calculate the average salary of employees in the IT department.
Calculate the sum of salaries of employees whose salary is less than 60000.
Find the total number of employees in each department whose salary is greater than 50000.

Conclusion

In this tutorial, we have learned about the SQL COUNT(), AVG(), and SUM() functions, how to use them with the ALL and DISTINCT arguments, and with the GROUP BY, HAVING, and ORDER BY clauses. We have also learned how to use these functions with other aggregate functions like MIN, MAX, and AVG, and with expressions. We have also learned how these functions handle NULL values and how to include NULL values in the calculations.

Remember to practice the examples and questions provided to get a better grasp of these concepts.

Happy querying!