Common Aggregate Functions

What COUNT, SUM, AVG, MIN and MAX return, and how each one treats NULL values.

Apprentice · SQL · MySQL · Updated

By

SQL has five aggregate functions you will use all the time. Each one turns a column of many rows into a single value. These examples use the world database.

COUNT(*)

Returns a total count of all rows.

The following query returns a count of all countries in the world database.

SELECT COUNT(*)
FROM country;

COUNT(<column>)

Returns a count of all values in a column. NULL values are NOT counted.

The following query returns a count of all independent countries.

SELECT COUNT(IndepYear)
FROM country;

COUNT(DISTINCT <column>)

Returns how many different values a column holds. NULL values are not counted, and a value that repeats is counted only once.

The following query returns a DISTINCT count of all regions.

SELECT COUNT(DISTINCT Region)
FROM country;

SUM(<column>)

Returns the sum of all values in the specified column.

The following query returns the sum of the population in all countries.

SELECT SUM(Population) AS total_population
FROM country;

AVG(<column>)

Returns the average of all values in the specified column. NULL values are NOT included in the calculation.

The following query returns the average life expectancy of ALL countries.

SELECT AVG(LifeExpectancy) AS average_life_expectancy
FROM country;

MIN(<column>)

Returns the smallest value of the specified column in all rows. NULL values are NOT considered.

The following query returns the lowest GNP of ALL countries.

SELECT MIN(GNP) AS min_gnp
FROM country;

MAX(<column>)

Returns the largest value of the specified column in all rows, NULL values are NOT considered.

The following query returns the highest GNP of ALL countries.

SELECT MAX(GNP) AS max_gnp
FROM country;

Do it: size up the Beverages shelf

The buyer is reviewing the Beverages shelf (category 1). She wants three numbers in one row: how many beverages there are, the lowest price, and the highest price.

The products table (the Tables tab shows the SQL that makes it) has these rows:

product_name category_id unit_price
Chai 1 18.00
Chang 1 19.00
Aniseed Syrup 2 10.00
Chef Anton's Cajun Seasoning 2 22.00
Grandma's Boysenberry Spread 2 25.00
Guaraná Fantástica 1 4.50
Côte de Blaye 1 263.50
Ipoh Coffee 1 46.00

Five of them are in category 1: Chai, Chang, Guaraná Fantástica, Côte de Blaye and Ipoh Coffee. The lowest price is $4.50 and the highest is $263.50. So your query should give exactly this one row:

beverages cheapest priciest
5 4.5 263.5

Your job: write one SELECT query that gives these three numbers for the Beverages.

  • Show three columns, in this order: beverages (how many beverages), cheapest (the lowest unit_price) and priciest (the highest unit_price).
  • Look only at the rows whose category_id is 1.
  • The answer is one row.

Practiced live on the lesson page: write the SQL right there and check it.

Cheat sheets