MariaDB Crash Course - Ben Forta [36]
Input
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products;
Output
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
Analysis
Here a single SELECT statement performs four aggregate calculations in one step and returns four values (the number of items in the products table; and the highest, lowest, and average product prices).
* * *
Tip: Naming Aliases
When specifying alias names to contain the results of an aggregate function, try not to use the name of an actual column in the table. Although there is nothing actually illegal about doing so, using unique names makes your SQL easier to understand and work with (and troubleshoot in the future).
* * *
Summary
Aggregate functions are used to summarize data. MariaDB supports a range of aggregate functions, all of which can be used in multiple ways to return just the results you need. These functions are designed to be highly efficient, and they usually return results far more quickly than you could calculate them yourself within your own client application.
13. Grouping Data
In this chapter, you learn how to group data so you can summarize subsets of table contents. This involves two new SELECT statement clauses: the GROUP BY clause and the HAVING clause.
Understanding Data Grouping
In the last chapter, you learned that the SQL aggregate functions can be used to summarize data. This enables you to count rows, calculate sums and averages, and obtain high and low values without having to retrieve all the data.
All the calculations thus far were performed on all the data in a table or on data that matched a specific WHERE clause. As a reminder, the following example returns the number of products offered by vendor 1003:
Input
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id = 1003;
Output
+-----------+
| num_prods |
+-----------+
| 7 |
+-----------+
But what if you want to return the number of products offered by each vendor? Or products offered by vendors who offer a single product, or only those who offer more than ten products?
This is where groups come into play. Grouping enables you to divide data into logical sets so you can perform aggregate calculations on each group.
Creating Groups
Groups are created using the GROUP BY clause in your SELECT statement. The best way to understand this is to look at an example:
Input
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
Output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
Analysis
The previous SELECT statement specifies two columns, vend_id, which contains the ID of a product’s vendor, and num_prods, which is a calculated field (created using the COUNT(*) function). The GROUP BY clause instructs MariaDB to sort the data and group it by vend_id. This causes num_prods to be calculated once per vend_id rather than once for the entire table. As you can see in the output, vendor 1001 has 3 products listed, vendor 1002 has 2 products listed, vendor 1003 has 7 products listed, and vendor 1005 has 2 products listed.
Because you used GROUP BY, you did not have to specify each group to be evaluated and calculated. That was done automatically. The GROUP BY clause instructs MariaDB to group the data and then perform the aggregate on each group rather than on the entire result set.
Before you use GROUP BY, here are some important rules about its use that you need to know:
• GROUP BY clauses can contain as many columns as you want. This enables you to nest groups, providing you