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

The COUNT() function returns the number of rows that matches a specified criterion. 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.

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

The COUNT function returns the total number of values in the specified field. It works on both numeric and non-numeric data types. All aggregate functions by default exclude nulls values before working on the data. COUNT (*) is a special implementation of the COUNT function that returns the count of all the rows in a specified table. COUNT (*) also considers Nulls and duplicates.

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.

The SUM() function returns the total sum of a numeric column. 

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

the MySQL SUM function which returns the sum of all the values in the specified columnSUM works on numeric fields onlyNull values are excluded from the result returned.

Demo Database

Below is a selection from the “Products” table in the Northwind sample database:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18
2Chang1124 – 12 oz bottles19
3Aniseed Syrup1212 – 550 ml bottles10
4Chef Anton’s Cajun Seasoning2248 – 6 oz jars22
5Chef Anton’s Gumbo Mix2236 boxes21.35

COUNT() Example

The following SQL statement finds the number of products:

Example

SELECT COUNT(ProductID)
FROM Products;

Note: NULL values are not counted.

AVG() Example

The following SQL statement finds the average price of all products:

Example

SELECT AVG(Price)
FROM Products;

Note: NULL values are ignored.

Demo Database

Below is a selection from the “OrderDetails” table in the Northwind sample database:

OrderDetailIDOrderIDProductIDQuantity
1102481112
2102484210
310248725
410249149
5102495140

SUM() Example

The following SQL statement finds the sum of the “Quantity” fields in the “OrderDetails” table:

Example

SELECT SUM(Quantity)
FROM OrderDetails;

More 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:

1SELECT COUNT(*) FROM ConferenceGuests;

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,

1SELECT 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?

1SELECT COUNT(DISTINCT State) FROM ConferenceGuests WHERE Country='India';

The output is as follows,Learn more

 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:

1SELECT COUNT(Name) FROM ConferenceGuests WHERE Name LIKE 'A%';

The output is as follows,

Count Like Clause

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

1SELECT SUM(Salary) FROM Employee;

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

Sum Simple Example

2. Using SUM() With the WHERE Clause

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

1SELECT SUM(Salary) FROM Employee WHERE Department='Operations';

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:

1SELECT SUM(Salary) FROM Employee WHERE Office_Code IN ('MUM', 'PUN');

We get the following output,

Sum In Operator

More Examples of MySQL AVG()

1. Simple Example of AVG()

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

1SELECT AVG(Salary) FROM Employee;

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?

1SELECT AVG(Salary) FROM Employee WHERE Department='Executive';
MySQL Aggregate functions Avg Where Clause
MySQL COUNT(), AVG() and SUM() Functions
Show Buttons
Hide Buttons