MySQL Aggregate Functions – COUNT(), SUM() and AVG()

By /
Count Sum Avg

In this tutorial, we will learn about the following MySQL aggregate functions – COUNT(), SUM() and AVG(). Suppose we want to count the number of employees from a table who are in the Marketing department. Or what if we wanted to find the average salary of employees in a company?

How about situations in which we would like to calculate the total amount spent by a company paying its employees? MySQL provides us with aggregate functions like COUNT(), SUM() and AVG() for these purposes.

  • COUNT() is used to count the number of rows for a given condition. COUNT() works on numeric as well as non-numeric values.
  • SUM() is used to calculate the total sum of all values in the specified numeric column.
  • AVG() is used to calculate the average value of the specified numeric column.

Note that, all three functions ignore NULL values.

Syntax of MySQL Aggregate Functions

Let’s go over the syntax of all the three functions here.

1. Syntax for COUNT()

SELECT COUNT(expression) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

2. Syntax for SUM()

SELECT SUM(column_name) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

3. Syntax for AVG()

SELECT AVG(column_name) FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

You will notice that only the COUNT() function has expression in its argument as opposed to column_name in SUM() and AVG(). This is because we have a special implementation of COUNT() which is COUNT(*). COUNT (*) returns the count of all the rows, including the ones with NULL values, in a specified table.

Examples of MySQL COUNT()

Consider the below ConferenceGuests table.

Count Conference Table
Conference Table

1. Simple example using COUNT()

Let us begin with a simple example demonstrating the COUNT() function. Let us count the number of records in the above table. We do so using the SELECT query as shown below:

SELECT COUNT(*) FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Count Simple Example

If we look closely at the ConferenceGuests table, we see that the record for guest with ID – 9 is missing. Hence, there are 15 records.

2. Counting Unique Values In A Column

Let us count the unique values in the Country column. For this, we will use the DISTINCT Keyword. We use the following query,

SELECT COUNT(DISTINCT Country) FROM ConferenceGuests;

The COUNT() function only counts the unique values in the Country column and returns the output as follows,

Count Distinct Example

3. Using COUNT() With the WHERE Clause

Let us now use COUNT() and specify a condition using the WHERE clause. How many unique states from India are represented in the conference?

SELECT COUNT(DISTINCT State) FROM ConferenceGuests WHERE Country='India';Code language: SQL (Structured Query Language) (sql)

The output is as follows,

MySQL Aggregate functions Count Where Clause

4. Using COUNT() With the LIKE Clause

Let us take a more complex example. Let us use the LIKE clause with the COUNT() function. How about finding the number of guests whose name begins with an ‘A’. The query for that is:

SELECT COUNT(Name) FROM ConferenceGuests WHERE Name LIKE 'A%';Code language: SQL (Structured Query Language) (sql)

The output is as follows,

Count Like Clause

Examples of MySQL SUM()

Consider the below Employee table.

MySQL Aggregate functions Sum Employee Table
Employee Table

1. Simple Example of SUM()

Let us calculate the sum of the Salary column. We do so using the query,

SELECT SUM(Salary) FROM Employee;Code language: SQF (sqf)

The sum of the Salary column is returned in the output below,

Sum Simple Example

2. Using SUM() With the WHERE Clause

How about finding the sum of salaries of all employees in the Operations department?

SELECT SUM(Salary) FROM Employee WHERE Department='Operations';Code language: SQL (Structured Query Language) (sql)

We get the following output,

Sum Where Clause

3. Using SUM() with the IN Operator

Let us take a little complex example now. How about finding the salaries of employees who have ‘MUM’ and ‘PUN’ as their Office_Code values? We will take the help of the IN Operator and the query is as follows:

SELECT SUM(Salary) FROM Employee WHERE Office_Code IN ('MUM', 'PUN');Code language: SQL (Structured Query Language) (sql)

We get the following output,

Sum In Operator

Examples of MySQL AVG()

Let us continue with the Employee table.

1. Simple Example of AVG()

Let us find the average salary of all employees in the company. We use the below query:

SELECT AVG(Salary) FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get the output as:

Avg Simple Example MySQL Aggregate functions

As you can see, we get the average value of the Salary column.

2. Using AVG() with the WHERE Clause

Just like COUNT() and SUM(), AVG() can be used with different clauses. Here, we will demonstrate how we use it with the WHERE clause. How about finding out the average salary in the Executive department?

SELECT AVG(Salary) FROM Employee WHERE Department='Executive';Code language: SQL (Structured Query Language) (sql)

And we get the output as follows,

MySQL Aggregate functions Avg Where Clause

Conclusion

MySQL aggregate functions like COUNT(), SUM() and AVG() prove essential in data analysis in large tables and databases. I would highly recommend you to check out the below reference link.

References