MySQL in a Nutshell, 2nd Edition

Analyzing and Manipulating Data

With MySQL you can not only retrieve raw data, but also analyze and format the data retrieved. For instance, suppose we want to know how many titles we stock by Leo Tolstoy. We could enter a SELECT statement containing a COUNT() function like this:

SELECT COUNT(*)
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Tolstoy';
   
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+

As another example, suppose that after setting up our database and putting it to use we have another table called orders that contains information on customer orders. We can query that table to find the total sales of a particular book. For instance, to find the total revenues generated from, say, William Boyd’s book Armadillo, we would enter the following SQL statement in the mysql client:

SELECT SUM(sale_amount) AS 'Armadillo Sales'
FROM orders
JOIN books USING(book_id)
JOIN authors USING(author_id)
WHERE title = 'Armadillo'
AND author_last = 'Boyd';
   
+-----------------+
| Armadillo Sales |
+-----------------+
|          250.25 |
+-----------------+

Here we are joining three tables together to retrieve the desired information. MySQL selects the value of the sale_amount column from each row in the orders table that matches the criteria of the WHERE clause. Then it adds those numbers and displays the sum with the column heading given.

For columns that contain date or time information, we can decide on the format for displaying the data using a variety of functions. For instance, ...