[MySQL] Note(2) AVG, COUNT, MAX, MIN, SUM

Last Updated on 2021-05-15 by Clay

Today I record some commonly used instructions of SQL:

• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()

AVG

AVG() is a instruction to find the value average. We also use the "world" database built in MySQL to demo.

select
    Name, SurfaceArea
    from country;

Output:

Omitted some showed data. We can see the country's name and surface area in this table.

At this time, we can use AVG() instruction to find the average value of land area of all countries.

select
    avg(SurfaceArea)
    from country;

Output:

COUNT

COUNT() has actually been used in the experience recorded before. The understanding is calculating how many records are in the query field.

For example, we calculate how many pieces of data (regardless of column) are in the city table:

select
    count(*)
    from city;

Output:

We can see there are 4,079 records in total.

In addition, the COUNT() instruction is often used together with the DISTINCT() instruction. DISTINCT() can exclude duplicate data and only count non-duplicate data.

For example, we saw there are 4,079 data, and we can count how many kinds of CountryCode:

select
    count(distinct(CountryCode))
    from city;

Output:

We get a smaller number, because the DISTINCT() instruction does not calculate duplicate values.

SUM

SUM() instruction need to discuss with COUNT() instruction, These two instructions look similar, but still have difference.

• SUM() return the sum of the values of all data
• COUNT() return how many data we search

Let's do a simple experiment. sum(SurfaceArea)/count(SurfaceArea) should be equal to avg(SurfaceArea).

select
    avg(SurfaceArea), sum(SurfaceArea)/count(SurfaceArea)
    from country;

Output:

they are the same.

MAX

MAX() instruction is very simple. It can find the largest value in the search field.

For example, we can use it to find the largest country surface area:

select
    max(SurfaceArea)
    from country;

Output:

MIN

MIN() almost as simple as MAX(), it finds the smallest value in the search field.

select
    min(SurfaceArea)
    from country;

Output: