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

sql-aggregate-functions

The SQL COUNT(), AVG() and SUM() Functions

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

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

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

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

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.


The SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(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

MIN() Example

The following SQL statement finds the price of the cheapest product:

Example

SELECT MIN(Price) AS SmallestPrice
FROM Products;

MAX() Example

The following SQL statement finds the price of the most expensive product:

Example

SELECT MAX(Price) AS LargestPrice
FROM Products;