Grouping Selected Results


In the preceding chapter, two different clausesORDER BY and LIMITwere introduced as ways of affecting the returned results. The former dictates the order in which the selected rows are returned; the latter dictates which of the selected rows are actually returned. This next clause, GROUP BY, is different in that it works by grouping the returned data into similar blocks of information. For example, to group all of the URLs by category, you would use

 SELECT * FROM url_associations, urls  WHERE url_associations.url_id=urls.  url_id GROUP BY url_category_id 

The returned data is altered in that you've now aggregated the information instead of returned just the specific itemized records. So where you might have seven URLs of one type, the GROUP BY would return all seven of those records as one row. You will often use one of several grouping (or aggregate) functions with GROUP BY. Table 5.7 lists these.

Table 5.7. MySQL's grouping functions.

Grouping Functions

FUNCTION

USAGE

PURPOSE

AVG()

AVG(column)

Returns the average value of the column.

COUNT()

COUNT(column)

Counts the number of rows.

MAX()

MAX(column)

Returns the largest value from the column.

MIN()

MIN(column)

Returns the smallest value from the column.

SUM()

SUM(column)

Returns the sum of all the values in the column.


You can apply combinations of WHERE, ORDER BY, and LIMIT conditions to a GROUP BY, normally structuring your query like this:

 SELECT what_columns FROM table WHERE condition GROUP BY column ORDER BY column LIMIT x, y 

To group data

1.

Select all of the titles that have been submitted (Figure 5.23).

 SELECT url FROM urls AS u, url_associations AS ua WHERE u.url_id=ua.url_id GROUP BY u.url_id; 

Figure 5.23. This GROUP BY query returns records grouped by URL, without using an aggregate function.


This is one of many ways to achieve the desired result of returning the URLs currently in the urls table. Whereas these URLs have been submitted (using the earlier INSERT statements) as eight distinct records (with different categories), only four distinct URLs were used.

2.

Count the number of times each URL is listed (Figure 5.24).

 SELECT url, COUNT(*) AS Number FROM urls AS u, url_associations AS ua WHERE u.url_id=ua.url_id GROUP BY u.url_id; 

Figure 5.24. This GROUP BY query counts the number of times each URL is listed (in other words, how many categories each URL is associated with).


This query is an extension of that in Step 1, adding the COUNT() function to return a number for how many times each URL has been submitted (each submission is a different type). COUNT() is normally but not necessarily applied to every column (*).

3.

Sort the URLs by the number of times they are listed (Figure 5.25).

 SELECT url, COUNT(*) AS Number FROM urls AS u, url_associations AS ua WHERE u.url_id=ua.url_id GROUP BY u.url_id ORDER BY Number DESC; 

Figure 5.25. Finally, a sort is added to organize the URLs by their number of listings.


With grouping, you can order the results as you would with any other query. Assigning the value of COUNT(*) as the alias Number facilitates this process.

Tips

  • NULL is a peculiar value and it's interesting to know that GROUP BY will group NULL values together, since they have the same nonvalue.

  • The COUNT() function will count only nonnull values. Be certain to use it on either every column (*) or on columns that will not contain NULL values (like the primary key).

  • The GROUP BY clause, and the functions listed here, take some time to figure out, and MySQL will report an error whenever your syntax is inapplicable. Experiment within the mysql client to determine the exact wording of any query you might want to run in an application.

  • Another related clause is HAVING, which is like a WHERE condition applied to a group.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net