In addition to fetching individual values, the SELECT statement is also able to aggregate data elements based on one or more columns. This installment on the Navicat Query Builder describes how to include native SQL aggregate functions in your queries to display column statistics.

About the Sakila Sample Database

As with previous installments, the queries that we'll be building here today will run against the Sakila sample database. It contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

Using Aggregate Functions

In SQL, output fields may be passed to aggregate functions to produce statistics for the column data. Aggregate functions include COUNT, MAX, MIN, SUM, and AVG:

• COUNT(): Returns the number of rows containing non-NULL values in the specified field.
• SUM(): Returns the sum of the non-NULL values in the specified field.
• AVG(): Returns the average of the non-NULL values in the specified field.
• MIN(): Returns the minimum of the non-NULL values in the specified field.
• MAX(): Returns the maximum of the non-NULL values in the specified field.

As touched upon in Part 2, clicking the <func> modifier the the left of an output field in the Navicat Query Builder opens a list of SUM, MAX, MIX, AVG, and COUNT aggregate functions. Selecting the desired function from the list will insert it into the query:

Here is a query that uses aggregate functions to display the number of films, average film length, total film length, as well as the minimum and maximum rental rates:

Setting Grouping Criteria

The above results pertain to the entire table. It is also possible to group records by one or more columns using the GROUP BY clause.

Let's design a query to show a count of rented films by month. In the Query Builder:

• Drag the film and rental tables into the editor.
• Join the two tables on the film.film_id and rental.inventory_id fields by dragging the former over to the latter.
• Add an output field. In the editor, enter "MONTHNAME(rental_date)".
• Click on the <Alias> label and enter a value of "rental_month".
• Add a second field. This time, select rental_id from the field list.
• Click the <Func> label and choose COUNT from the list.
• Click on the <Alias> label and enter an Alias of "rental_count".
• Click on the <Click here to add GROUP BY> label and use the editor to enter "MONTH(rental_date)".
  
  The Query Builder should now look like this:
  
• Click OK to close the Query Builder and return to the Query Editor.

Run the query to view the results:

Notice how applying the MONTHNAME function on the rental_month output field displays the full month name rather than the month number as the MONTH() function does. In any event, either function could be employed to group results by month.