3.7. 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 Tolstoy. We could enter a SELECT statement containing a COUNT() function like this:

SELECT COUNT(*) FROM books, authors WHERE author_last = 'Tolstoy'    AND author_id = authors.rec_id;     +----------+ | 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, books, authors WHERE  title = 'Armadillo'    AND author_last = 'Boyd'    AND book_id = books.rec_id    AND author_id = authors.rec_id;     +-----------------+ | Armadillo Sales | +-----------------+ |          250.25 | +-----------------+

Here we are joining three tables together to retrieve the desired information. MySQL is selecting 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. Most column names appear in only one table, so MySQL knows what we mean even if we don't specify the table each column is in; for a couple of columns we need to use the table.column format.

For columns that contain date or time information, we can format how the data is displayed using a variety of functions. For instance, suppose that we want to extract from the orders table the date that a customer made a particular purchase based on his receipt number (e.g., 1250), which in turn is the record identification number or rec_id. We could simply enter the following statement and get the default format as shown in the last line of results:

SELECT purchase_date AS 'Purchase Date'  FROM orders WHERE rec_id = '1250';     +---------------+ | Purchase Date | +---------------+ | 2004-03-01    | +---------------+

This format (year-month-day) is understandable. However, if we want the month displayed in English rather than numerically, we would have to use a date function:

SELECT CONCAT(MONTHNAME(purchase_date), ' ',         DAYOFMONTH(purchase_date), ', ',         YEAR(purchase_date)) AS 'Purchase Date' FROM orders WHERE rec_id = '1250';     +---------------+ | Purchase Date | +---------------+ | March 1, 2004 | +---------------+

To put the date together in a typical human format used in the United States, we're using the CONCAT( ) function in conjunction with a few date functions. It may be a little confusing at first glance, because we're inserting a space between the month and the day at the end of the first line and a comma and a space after the day at the end of the second line. As for the date functions, the first one extracts the month from the purchase_date column and formats it to display it as the full name. The second date function on the second line extracts just the day so that we can put spaces around it and a comma after it. The third date function on the third line extracts just the year. As you can see in the results, this works. However, it's not the cleanest method by which the date can be assembled. We could use the DATE_FORMAT( ) function instead:

SELECT DATE_FORMAT(purchase_date, "%M %d, %Y")        AS 'Purchase Date' FROM orders WHERE rec_id = '1250';

This is a much more efficient method and it provides the same output as the previous statement. You just have to know the formatting codes to be able to use this function properly. They're listed in Chapter 6, along with several more formatting codes.