The last feature that we will discuss about querying is the GROUP BY clause. This clause allows you to group rows of the same table with a common field. For example, let's say we want to know how many books each author has in just one query. Try the following:
mysql> SELECT -> author, -> COUNT(*) AS amount, -> GROUP_CONCAT(title SEPARATOR ', ') AS titles -> FROM book -> GROUP BY author -> ORDER BY amount DESC, author; +-----------------+--------+-------------------+ | author | amount | titles | +-----------------+--------+-------------------+ | George Orwell | 2 | 1984, Animal Farm | | Homer | 2 | Odyssey, Iliad | | Bram Stoker | 1 | Dracula | | Haruki Murakami | 1 | 1Q84 | | J. M. Barrie | 1 | Peter Pan | | Jodi Picoult | 1 | 19 minutes | +-----------------+--------+-------------------+ 5 rows in set (0.00 sec)
The GROUP BY clause, always after the WHERE clause, gets a field—or many, separated by a coma—and treats all the rows with the same value for this field, as though they were just one. Thus, selecting by author will group all the rows that contain the same author. The feature might not seem very useful, but there are several functions in MySQL that take advantage of it. In this example:
COUNT(*)is used in queries withGROUP BYand shows how many rows this field groups. In this case, we will use it to know how many books each author has. In fact, it always works like this; however, for queries withoutGROUP BY, MySQL treats the whole set of rows as one group.GROUP_CONCATis similar toCONCAT, which we discussed earlier. The only difference is that this time the function will concatenate the fields of all the rows of a group. If you do not specifySEPARATOR, MySQL will use a single coma. However, in our case, we needed a coma and a space to make it readable, so we addedSEPARATOR ', 'at the end. Note that you can add as many things to concatenate as you need inCONCAT, the separator will just separate the concatenations by rows.
Even though it is not about grouping, note the ORDER clause that we added. We ordered by two fields instead of one. This means that MySQL will order all the rows by the amount field; note that this is an alias, but you can use it here as well. Then, MySQL will order each group of rows with the same amount value by the title field.
There is one last thing to remember as we already presented all the important clauses that a SELECT query can contain: MySQL expects the clauses of the query to be always in the same order. If you write the same query but change this order, you will get an error. The order is as follows:
SELECTFROMWHEREGROUP BYORDER BY