Grouping queries
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...