10. Aggregate Functions

Overview

In this chapter, we'll use SQL aggregate functions and solve problems with them. By the end of this chapter, you'll be able to use SQL aggregate functions and the GROUP BY clause, implement the SQL HAVING clause, explain the differences between the HAVING and WHERE clauses, use the SQL OVER and PARTITION BY clauses, and implement the RANK and DENSE_RANK functions.

Introduction

In the previous chapters, we saw that the WHERE clause can be used to filter SQL query result sets in an efficient, almost intuitive way. For example, say we want to identify the total number of executives in a department, or we want the total number of dependents who are covered by a medical claim. In such cases, more than the details, we are looking for a single calculated value. For such cases, where we need calculated results based on database rows, we use SQL aggregate functions. They can also be used to calculate values across subsets of query result rows. We will also look at advanced clauses, such as the GROUP BY and the HAVING clauses, and see how they can help us to fine-tune our results.

Aggregate Functions (SUM, COUNT, AVG, MIN, and MAX) and the GROUP BY Clause

MySQL provides functions that return single calculated values based on defined sets of values. We'll first look at the general way to use these functions in a SQL query, and then we'll examine each function individually:

• SUM: Adds, or sums, relevant values
• COUNT: Returns a count of the relevant values
• AVG (average): Calculates the average of a set of relevant values
• MIN (minimum value): Returns the lowest value of a set of values
• MAX (maximum value): Returns the highest value of a set of values

Say that a store manager wants the average weight of the products in each PACKT_ONLINE_SHOP product category. In the PACKT_ONLINE_SHOP database, each ProductCategory table row maps to one or more products in the Products table. We can start with this query:

USE     packt_online_shop;
SELECT  PC.ProductCategoryID, PC.ProductCategoryName, P.UnitKGWeight         AS 'PRODUCT...

The HAVING Clause

As we saw, the aggregate functions will solve many problems for us. Eventually, however, we'll want to filter the aggregate function value of a query result set. We have learned how to build SQL queries that calculate aggregate function values. If we want to filter the query results on those aggregate function values, the WHERE clause won't work. For example, we might want to see the query results of a SQL MAX function that land below a specific value. We might need the query results of a SQL AVG function that match a specific value. The HAVING clause will help. Starting with this query, suppose we want only those rows with MINIMUM NET RETAIL PRICE values greater than 5.00 in the result set:

USE        packt_online_shop;
SELECT     PC.ProductCategoryID, PC.ProductCategoryName,           AVG(P.UnitKGWeight) AS 'AVERAGE PRODUCT KG WEIGHT',           MIN(P.NetRetailPrice) AS 'MINIMUM NET RETAIL PRICE'
FROM       ProductCategories PC INNER...

Summary

In this chapter, we learned that SQL aggregate functions provide an efficient, flexible way to calculate summary values in SQL query result sets. We discovered that the GROUP BY clause will separate the results of a SELECT query into one or more row groups to support the design and execution of queries with aggregate functions. We saw that the HAVING clause provides a pinpoint way to filter aggregate function calculations. Although they're similar, we learned about the differences between WHERE and HAVING clauses. Finally, we saw that when we use OVER and PARTITION BY in creative ways, MySQL will efficiently expand the range of problems it can solve for us. We can also order the items with a group using the RANK and DENSE RANK functions. In the next chapter, we will look at some advanced features of SQL programming.