In-Depth Notes on Aggregate Functions and Grouping in SQL

Aggregate Functions in Database Systems

Aggregate functions, or group functions, are essential tools in SQL for summarizing data from multiple rows into a single value. This differs fundamentally from standard functions, which return a value for each row. Here we will explore the most important aggregate functions, their usage, and the concept of grouping.

Key Aggregate Functions

COUNT function
- The most common use of aggregate functions is to count the number of rows in a table. For example:
```
 SELECT COUNT(*) FROM BOOK_LIBRARY.books;
```
This query counts all the rows in the books table.
- The asterisk argument means it counts all rows, regardless of filtering. If filtering conditions are applied, as in:
```
 SELECT COUNT(*) FROM BOOK_LIBRARY.books WHERE price < 3000;
```
it only counts the rows where the price is less than 3000.
- Different ways to use COUNT include counting where specific criteria are met. For instance:
```
 SELECT COUNT(topic) FROM BOOK_LIBRARY.books; -- Counts non-NULL topics
```
This counts only those rows where the topic field is not NULL.
COUNT(DISTINCT)
- To count unique values in a column:
```
 SELECT COUNT(DISTINCT topic) FROM BOOK_LIBRARY.books;
```
This counts different, non-NULL topics.
MIN and MAX functions
- Use these functions to find the minimum and maximum values in a column, respectively:
```
 SELECT MIN(price), MAX(price) FROM BOOK_LIBRARY.books;
```
They work with sortable types and return the lowest and highest values.
SUM and AVG functions
- These functions calculate the total and the average of a numeric column:
  sql SELECT SUM(price), AVG(price) FROM BOOK_LIBRARY.books;
  Both functions ignore NULL values. For the AVG, it computes the mean of valid prices.

Grouping with Aggregate Functions

When you want to compute aggregates for groups of rows, you can use the GROUP BY clause. For instance, if you want to know how many books there are per topic and their average price, use:

   SELECT topic, COUNT(*), ROUND(AVG(price), 2) FROM BOOK_LIBRARY.books GROUP BY topic;

This SQL query provides counts and average prices per distinct topic, including a potential group for books with a NULL topic.

Understanding Grouping Mechanics

Temporary Table Creation: SQL first creates a temporary table based on the results of the WHERE clause, applying any filters before grouping the data.
Sorting and Group Creation: Rows with the same grouping values are sorted together to form groups.
Aggregation: For each group, aggregate functions are computed, producing a single output row for each distinct grouping.
Output Ordering: Finally, you can organize the output table with the ORDER BY clause, which refers to the aggregated results.

Using HAVING Clause for Group Filtering

After grouping, if you want to filter those groups, use HAVING. For example, to find topics with more than one book:

   SELECT topic, COUNT(*) FROM BOOK_LIBRARY.books GROUP BY topic HAVING COUNT(*) > 1;

This illustrates the difference between WHERE, which filters rows before grouping, and HAVING, which filters after groups are created.

Complications with Aggregate Functions

Aggregate functions like COUNT, MIN, MAX, SUM, and AVG do not return a result if all rows are NULL, except for COUNT, which returns 0. When using conditional aggregation, it’s crucial to understand how NULL values affect results.

Examples of Real Queries with Subqueries and Grouping

Finding oldest members:

   SELECT last_name, first_name, birth_date FROM BOOK_LIBRARY.customers WHERE birth_date = (SELECT MIN(birth_date) FROM BOOK_LIBRARY.customers);

Checking average royalties:

   SELECT author_id, royalty FROM BOOK_LIBRARY.writing WHERE royalty > (SELECT AVG(royalty) FROM BOOK_LIBRARY.writing) ORDER BY royalty DESC;

These show how aggregate functions can be combined with subqueries.

Conclusion

Aggregate functions and grouping are integral components of SQL that allow efficient data summarization. Understanding their mechanics can significantly empower your database querying skills.