MySQL - aggregate functions

What is an aggregate function?

Aggregate functions in MySQL, are built in functions that allow you to perform a calculation on a set of values, and return a single value.
MIN(), MAX(), SUM(), AVG() and COUNT() are just a few of the functions available for us to use - you can find a full list on the MySQL docs. These functions don't alter the data within a table, only the results of the query.

Why do aggregate functions use GROUP BY?

The GROUP BY clause is used to arrange rows into groups. The results of the queries using aggregate functions are returned in groups specified by the GROUP BY syntax. For example, if you had a table of books with each book having a title and author column, and you wanted to find out how many books each author had published, you would group by the authors name. So books with the same author would be grouped together and the authors name would only be returned once. The query might look something like this -

SELECT author_name, COUNT(*) 
FROM books 
GROUP BY author_name;

Aggregate functions must have a GROUP BY specified, to group the results that have the same info together and, unless otherwise stated, most aggregate functions will ignore NULL values. COUNT() is one function that does include NULL values.

How do you use an aggregate function?

The basic syntax for using an aggregate function is - SELECT <aggregate function>(<column name>) FROM <table name> GROUP BY <column name>; The column that you want to perform the function on sits inside the functions brackets, with the GROUP BY clause coming at the end of the query. For example, if you had a table of books with each book having a title, author_first_name, author_last_name and book_release_year columns, and you wanted to find out their newest released book, the query might look something like this-

SELECT author_last_name, MAX(book_release_year) 
FROM books 
GROUP BY author_first_name, author_last_name;

Grouping by the first and last names will return all authors with different first but the same last names, therefore avoiding grouping authors with the same last names together.