ANSI SQL includes several aggregate functions, which allow you to perform a calculation on a set of values to return their result as a single value. These include Count(), Min(), Max(), Sum() and AVG(), and others. By default, aggregate functions apply to all rows, but you can narrow down the field by applying a WHERE clause to the SELECT statement. Moreover, you can conditionally select certain rows using a few more techniques that we'll explore here today using Navicat Premium. These include the use of a CASE statement as well as the GROUP BY clause. We'll apply these techniques on the AVG() function, but they will work equally well with all aggregate functions.
Using the AVG() Function
The AVG() retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL. We'll run our queries against the Sakila sample database. It was originally developed for MySQL, but has since been ported to most popular DBMSes. Navicat Premium is the ideal database client to use here because it supports everything from MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL, to SQLite. Moreover, it's compatible with cloud databases like Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, Google Cloud and MongoDB Atlas as well.
The film table stores information about individual films for the fictional Sakila video rental store. Columns include title, description, running time, rental cost, rating, and others.
We can use the AVG() function to determine the average rental cost for ALL films as follows:
Using the CASE Statement
The AVG() function accepts an expression. Hence, it can be a column name, but it can be any valid expression. Therefore, we can apply the AVG() function conditionally by passing a CASE statement to the AVG() function as a parameter. We could determine the average rental_rate for only those films that have a PG rating using a CASE statement like so:
The above query shows the total number of films, films that do not have a PG rating, and the average rental rate for all films as well as those with a PG rating. The CONCAT() and FORMAT() functions are employed to display the rental_rate as currency.
Using the GROUP BY Clause
Another way to apply AVG() to only certain rows is to use GROUP BY. It aggregates the results on the basis of selected column. Hence, grouping results by the rating will list the average rental_rate for each rating:
We could narrow down the rows selected further by using a WHERE and/or HAVING clause(s). Both may be employed separately or in tandem. For instance, the next query selects films with a language_id of 1 (English) whose count by rating total less than 200:
Conclusion
In today's blog we employed the CASE statement and GROUP BY clause to conditionally list film records based on averages.
Queries were executed in Navicat Premium 15. It adds over 100 enhancements and includes several new features to give you more ways that ever to build, manage, and maintain your databases than ever before!
