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

Introduction

In our previous blog, we learned how to control result size with LIMIT and how to find the smallest and largest values using MIN() and MAX(). Those skills help you show the right slice of data. In this blog, we level up with two powerful topics used in almost every report:

Aggregate functions — COUNT(), AVG(), and SUM() to answer “how many?”, “what is the average?”, and “what is the total?”.
Pattern matching — the LIKE operator with wildcards (% and _) to search text flexibly.

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

What they do

COUNT(): This function returns how many rows match your query. Variants: COUNT(*), COUNT(column), and COUNT(DISTINCT column).

COUNT() Syntax:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG(): This function returns the average of a numeric column. Ignores NULL values.

AVG() Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM(): This function returns the total of a numeric column. Ignores NULL values.

SUM() Syntax:
SELECT SUM(column_name)
FROM table_name
WHERE condition;

Demo Database (Orders & Products)

We will use the “Products” table in the Northwind sample database:

ProductID	ProductName	Category	Price
1	Chai	Beverages	18
2	Chang	Beverages	19
3	Aniseed Syrup	Condiments	10
4	Chef Anton’s Cajun Seasoning	Condiments	22
5	Ikura	Seafood	31
6	Konbu	Seafood	6

COUNT() Examples

SELECT COUNT(ProductID)
FROM Products;

Result:

Count(ProductID)
6

NULL values are not taken into account.

AVG() Examples

SELECT AVG(Price)
FROM Products;

SUM() Examples

SELECT SUM(Quantity)
FROM OrderDetails;

Request more information

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