SQL, optional: Aggregates and Grouping

This one is optional. In class, COUNT, SUM, AVG, MIN, MAX and GROUP BY were reading vocabulary: you need to recognize them when a chat writes them, not write them yourself. If you want to know how they work, here they are. It picks up where SQL 1 left off, with the same 👶 baby tables.

Aggregate Functions

Often, the questions you want answered will involve math. That is, instead of simply seeing a subset of the rows in a table, we'll want to aggregate the results together in some way, e.g. adding the number of rows, summing a single column, seeing an average, etc.

How many reviews have been written?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - not applicable in this case, since we're looking for an aggregate number
• Add any other conditions - none
• Then, ask the question:

SELECT COUNT(*) FROM Reviews;

There are a couple of crazy things going on here! Let's break it down.

First, instead of a column name like Name, Price, Body, etc. in our SELECT clause, we're calling a SQL function. There are many SQL functions built into SQL, but they will always contain a set of parentheses, which denote arguments to the function. In this case, we're using the COUNT function, which accepts an argument of "what columns are we counting?" Since we're simply looking for the aggregate number of rows in the table, the columns aren't relevant and we just use *.

And even though we're asking for an aggregate, that is, a single number representing the count of all the reviews, our result is still a 👶 baby table. It simply contains a single column -- called COUNT(*) -- and a single row. We can call several SQL functions, or a combination of SQL functions and column names, which will result in a 👶 baby table containing several columns*.*

Let's look at another aggregate function, where the argument does matter:

What is the average rating across all reviews?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - an average Rating
• Add any other conditions - none
• Then, ask the question:

SELECT AVG(Rating) FROM Reviews;

Similar as before, with our COUNT function, but this time, the argument matters. We're using the AVG function, which takes a column name as an argument - which column are we averaging?

As mentioned, there are many functions built-in to SQL. The ones that are relevant to this lesson are aggregate functions - ones that do math to aggregate information. Examples of aggregate functions include:

• COUNT - counts the number of rows
• AVG - average of a column value or expression
• SUM - sum of a column value or expression
• MIN - minimum of a column value or expression
• MAX - maximum of a column value or expression

Grouping

Occasionally, a question like How many reviews have been written? will be enough to satisfy business requirements. However, it's more likely that your boss, client, or other stakeholder will want more specific information than simply aggregating the entire table's worth of data. We'll often want to categorize or aggregate (group) results by the value of another column. For example, a more common question might be:

How many reviews have been written per product?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate number of reviews
• Add any other conditions - none
• Then, ask the question:

SELECT Product, COUNT(*) 
FROM Reviews
GROUP BY Product;

We see that the values separated by commas in the SELECT clause, whether they are column names or SQL functions, end up being the columns in the 👶 baby table. And, because we used a GROUP BY clause, our results are aggregated (grouped) by the distinct values in the Product column.

This brings us to Rule #1A, the First Law of all SQL... when using a GROUP BY clause, the columns listed in the GROUP BY must also be in the SELECT clause. Makes sense, right? In this example, if the Product column wasn't also in the SELECT, the results would just be a list of numbers with no context:

SELECT COUNT(*) 
FROM Reviews
GROUP BY Product;

Not very useful, is it?

The rule in the other direction is the one that actually bites: every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Most databases, PostgreSQL included, refuse to run a query that breaks it. SQLite runs it anyway, and quietly fills in a value from one row of each group. In the baseball database:

SELECT year, name, MAX(wins)
FROM teams
WHERE year = 2002
GROUP BY year;

The Oakland Athletics also won 103 games in 2002. One row per group has room for one name, so SQLite picked one and didn't mention it. When a chat hands you SQL with a column like name sitting next to a MAX or a COUNT, check whether that column is in the GROUP BY. If it isn't, the name may not mean what it looks like it means.

Order of Clauses

SQL 1 ended with the order of clauses; here it is again with GROUP BY in its place. Because of the way SQL is executed, the order in which the clauses are written does matter. This may differ slightly based on the flavor/implementation of SQL being worked with (i.e. some are more forgiving than others), but generally speaking, the order should be as follows:

• SELECT - the columns or executed functions you want in the result
• FROM - the table(s) involved
• WHERE - the conditions or constraints
• GROUP BY - columns in the SELECT used to aggregate the results
• ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
• LIMIT - how many rows to return

To put it all together:

What is the average rating per product, taking into account only those reviews with a rating of 3 or above, from lowest to highest average rating?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate average rating of reviews
• Add any other conditions - rating of 3 or above
• Then, ask the question:

SELECT Product, AVG(Rating)
FROM Reviews
WHERE Rating >= 3
GROUP BY Product
ORDER BY AVG(Rating);

Try it: aggregation

In class, aggregates were reading vocabulary: you will see them in SQL a chat writes for you far more often than you will write them. Reading is easier once you have written one or two, so:

• How many teams played in 2020? One COUNT(*) and one WHERE. (30.)
• Which team name has been used for the most seasons? GROUP BY name, COUNT(*), sorted, and one row. Then ask for three rows, and see what one row was hiding.