Grouping Data - MariaDB Crash Course - page 104

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 (cre-
ated 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 with more granular control
over how data is grouped.

■

■ If you have nested groups in your GROUP BY clause, data is summarized
at the last specified group. In other words, all the columns specified are
evaluated together when grouping is established (so you won't get data
back for each individual column level).