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.

### 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,

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,

### 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,

### 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,

## Examples of MySQL SUM()

Consider the below 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,

### 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,

### 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,**

## 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**:

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,

## 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

- MySQL official documentation on aggregate functions.