Aggregations and Group Operations

1. Aggregations and Group Operations

Welcome back! So far, we've learned how to retrieve and filter individual rows from a database. But what if a business analyst asks: "How many books do we have?" or "What's the average publication year?" Answering these questions row by row would be tedious. That's where aggregations come in.

2. Aggregations: COUNT, SUM, AVG

In SQL, aggregations are functions that summarize multiple rows into a single result. COUNT gives us the number of rows, SUM adds up numeric values, and AVG calculates an average. In this query, we use COUNT with an asterisk to count all rows, and AVG on publication_year to compute the average. The results show we have 14 books total, with an average publication year around 1999. Notice how many rows collapse into just one summary row.

3. GROUP BY for Summarization

But what if we want summaries for specific groups? For example, how many books were published each year? That's where GROUP BY comes in. It divides rows into groups before applying the aggregation. Here, we group by publication year. Now, instead of one total, we get one row per year. Looking at the results, 2018 had 3 books published, while other years had just one.

4. GROUP BY with Multiple Columns

Grouping isn't limited to a single column. We can group by multiple columns to create finer breakdowns. In this query, we group by both year and category name. Now we're answering: how many books were published each year, per category? Every unique combination of year and category becomes its own group.

5. Filtering Groups with HAVING

Sometimes we want to filter these grouped results. For instance, show only years with more than two books. The WHERE clause won't work here because it filters rows before grouping. To filter after aggregation, we use HAVING. Think of HAVING as WHERE, but for groups. Here, we keep only groups where the count exceeds two. The result shows just 2018, the only year that meets that condition.

6. Aggregations and groups in Java

From Java, running aggregation queries requires no new JDBC concepts. We still use a PreparedStatement and set parameters as before. In this example, we parameterize the HAVING threshold. With setInt, we pass 2, filtering for years with more than two books. The key difference is how we interpret the results. Since the query returns grouped data, we retrieve the year and count using their column aliases. This way, the database does the heavy lifting, and we only receive the summarized results. If we print the results, we would get only one entry that meets our conditions.

7. Let's practice!

Let's put these aggregation skills into practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.