Aggregate Functions

In the article about grouping, we discussed that when using the GROUP BY operator, we can use aggregate functions. Let's talk more about them 🐳.

An aggregate function is a function that performs a calculation on a set of values and returns a single value.

General structure of a query with an aggregate function

MySQL
SELECT [literals, aggregate_functions, grouping_fields]
FROM table_name
GROUP BY grouping_fields;

For example, a query using the AVG aggregate function might look like this:

MySQL
SELECT home_type, AVG(price) as avg_price FROM Rooms
GROUP BY home_type
home_typeavg_price
Private room89.4286
Entire home/apt148.6667
Shared room40

Description of aggregate functions

FunctionDescription
SUM(table_field)Returns the sum of the values
AVG(table_field)Returns the average value
COUNT(table_field)Returns the number of records
MIN(table_field)Returns the minimum value
MAX(table_field)Returns the maximum value

Aggregate functions apply to values that are not NULL. The exception is the COUNT(*) function..

Examples

  • Find the number of each type of home and sort the resulting list in descending order:

    MySQL
    SELECT home_type, COUNT(*) as amount FROM Rooms
    GROUP BY home_type
    ORDER BY amount DESC
    
    home_typeamount
    Private room28
    Entire home/apt21
    Shared room1
  • For each room, find the latest end date of reservations(the end_date field)

    MySQL
    SELECT room_id, MAX(end_date) AS last_end_date FROM Reservations
    GROUP BY room_id
    
    room_idlast_end_date
    12019-02-04T12:00:00.000Z
    22020-03-23T09:00:00.000Z
    132020-04-21T10:00:00.000Z
    162019-06-24T10:00:00.000Z
    212020-02-29T10:00:00.000Z
    192020-05-02T10:00:00.000Z
    82020-01-21T12:00:00.000Z
    72019-09-17T10:00:00.000Z
    52020-05-15T10:00:00.000Z
    502019-11-25T11:00:00.000Z
    492020-06-11T10:00:00.000Z
    482019-11-10T10:00:00.000Z
    322020-01-18T13:00:00.000Z
    172019-11-05T09:00:00.000Z
    252020-04-22T09:00:00.000Z
    142020-02-12T10:00:00.000Z
    392019-12-09T10:00:00.000Z
    382020-03-23T10:00:00.000Z

We are in touch with you
English