Learning PHP 7

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