Job Ready SQL

Lesson 13Grouping and Aggregates

SQL can compute aggregate values. Aggregates are single values calculated from many values. If you have a table of students with GPAs, you could calculate the average GPA. The average value is an aggregate. The minimum or maximum GPA could also be calculated. Both are aggregates. Regardless of the number of students, an aggregate “rolls up” or “cooks down” to one value.

The GROUP BY clause allows for the further partitioning of a result and for the calculation of aggregates per partition. Using GROUP BY, there is the ability to calculate the average GPA per home state, the minimum GPA per major, or the number of classes each student completed.

Learning Objectives

By the end of this lesson, you will be able to:

• Use the aggregate functions: COUNT, SUM, AVG, MIN, and MAX
• Group data using GROUP BY
• Filter groups with the HAVING clause
• Use multiple aggregates in the same query

The lesson will once again use the TrackIt database and schema. You will need to have it installed to run the queries provided in this lesson to see the results.

AGGREGATE FUNCTIONS

There are a dozen or more SQL aggregate functions, depending on what SQL database system you use. The following are the most common and universally supported:

• COUNT: Counts the number of non‐NULL values in a set; works on any non‐NULL value
• SUM: Sums values in a set; values must be numeric
• AVG: Calculates the average of values in a set; values must be numeric
• MIN: Determines the minimum ...