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

The COUNT() function returns the number of rows that matches a specified criterion.

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition; 

The AVG() function returns the average value of a numeric column. 

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition; 

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

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition; 

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; 

Note: NULL values are ignored.