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 🐳.

General structure of a query with an aggregate function

MySQL 8.4

SELECT { literal | aggregate_function | grouping_field } [, ...]
FROM table_name
GROUP BY grouping_field [, ...];

literal: A fixed value such as 'text' or 1. It is the same for every group.
aggregate_function: A function that computes one value from all rows of a group, for example AVG(price).
grouping_field: The column that splits rows into groups: rows with the same value end up in one group. You can list several columns separated by commas.
table_name: The table whose rows are grouped.

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

MySQL 8.4
SELECT home_type, AVG(price) as avg_price FROM Rooms
GROUP BY home_type

Function	Description
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

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

MySQL 8.4
SELECT home_type, COUNT(*) as amount FROM Rooms
GROUP BY home_type
ORDER BY amount DESC

For each room, find the latest end date of reservations (the end_date field)

MySQL 8.4
SELECT room_id, MAX(end_date) AS last_end_date FROM Reservations
GROUP BY room_id