Critical Skill 5.2 Order and Group Data

By default, MySQL returns data in the order in which it was placed into the table. Ordering and grouping data allows you to present reports in a form that conveys their information in a clear, easily readable manner.

Using the GROUP BY and ORDER BY Clauses

On the surface, the GROUP BY and ORDER BY clauses seem to be very similar, and in fact, they have much in common. Using one or the other arranges data in sequence by the specified column. Using both ORDER BY and GROUP BY together allows you to specify the sequence data is arranged in, and then group like data by referencing another specified column.

GROUP BY and ORDER BY have many similarities:

They both cause data to be arranged in an order using a specified column as the arrangement basis.
They both default to ascending order, but can be changed to descending order by using the DESC reserved word at the end of their syntax.
They both can take the alias, or header name, established by an AS clause in the same command, instead of the actual field name .
They both can take multiple columns as specifiers.

On the other hand, GROUP BY and ORDER BY have some differences:

GROUP BY has an additional clause called HAVING, which you can use to filter the data even further.
ORDER BY returns every instance of the specified field, but GROUP BY returns only one instance of each unique data item contained in the field.

Suppose that you have a table called monsters, which has a field m_type containing the following data: zombie, vampire, werewolf, mummy, and vampire. A SELECT command specifying the column m_type would return a different report depending on whether you used GROUP BY or ORDER BY to specify their output format.

 SELECT m_type FROM monsters ORDER BY m_type;

This returns the entire set of five values, including the repetition of the data vampire.

 SELECT m_type FROM monsters GROUP BY m_type;

This returns an edited set of four values, removing the repetition of the data vampire, grouping the repeated data into one instance.

If you are using both GROUP BY and ORDER BY in the same command, you must always place GROUP BY before ORDER BY, because that is required in the SELECT syntax.

Using Grouping Functions

Some functions are considered grouping functions, and their use can require a GROUP BY clause. All of the functions covered in the Using Aggregate Functions section earlier in this module fall in this category. If you use an aggregate or grouping function, and do not include a GROUP BY clause in the command, the result is the same as asking MySQL to group on all rows.

When you have a column in your output that is nongrouping and a column that is grouping, the GROUP BY clause is required. For instance, if you have a table called lazertag with the fields team, date, and score, and you want to make a report that shows the average score of each team listed in the table, you could use the following code:

 SELECT team, AVG(score) FROM lazertag GROUP BY team;

The team column of the SELECT is nongrouping; it merely reports the data from the table without manipulating it. The score column is part of an AVG clause, which groups the data from all the rows of the column and averages them into one result. Since there is a mixture of grouping and nongrouping data specified in the output, a GROUP BY clause is necessary. Figure 5-15 shows the result of this command, plus the error that results if you try to run it without its GROUP BY clause.

Figure 5-15: Using grouping and nongrouping columns

The error shown in Figure 5-15 gives a short list of the types of functions (MIN, MAX, COUNT) that require the GROUP BY clause. Here is a list of all of the functions that require the GROUP BY clause:

AVG	BIT_AND	BIT_OR
BIT_XOR	COUNT	GROUP_CONCAT
MIN	MAX	STD
STDDEV	SUM	VARIANCE

GROUP BY does not always operate intuitively, especially when it takes more than one field, so you may need to experiment until you become familiar with how it works. For example, consider these two commands:

 SELECT team, score, date FROM lazertag GROUP BY team, date; SELECT team, score, date FROM lazertag GROUP BY date, team;

Figure 5-16 shows how reversing the order of multiple GROUP BY fields in these commands changes the results. In the first example, the records are arranged by team, and then all the records from the same team are arranged by date. In the second example, all the records are arranged by date, and then all the records with the same date are arranged by team. The score field contents are just taken along for the ride.

Figure 5-16: Reversing the order of GROUP BY fields changes the results.

Screening Results with HAVING

GROUP BY also allows you to further filter the data by adding a HAVING clause; however, you should not use the HAVING clause when a WHERE clause will suffice. You should use the HAVING clause as the criteria only to determine which record or records sent from the GROUP BY clause should be used in the output. The WHERE clause should be used as the criteria to determine which record or records should be sent to the GROUP BY clause.

The following shows two variations of the HAVING clause:

 SELECT team, AVG(score) FROM lazertag GROUP BY team; SELECT team, AVG(score) FROM lazertag GROUP BY team HAVING AVG(score)>=600; SELECT team, AVG(score) FROM lazertag GROUP BY team HAVING team="bearz";

As shown in Figure 5-17, the resulting report lists the average score for each team playing lazertag, and then two variations of the HAVING clause further distill that information to display which team s average score was under 600, and then the average score of the bearz team.

Figure 5-17: Examples of the GROUP BY HAVING clause

Progress Check

1.	Which must come first in the SELECT syntax: GROUP BY or ORDER BY?
2.	Which of the grouping clauses has the HAVING clause?
3.	What is the main difference in the output of the GROUP BY and ORDER BY clauses?
4.	What keeps the grouping functions from grouping on all rows?
5.	When does using one of the grouping functions require also using the GROUP BY clause?

Answers

1.	GROUP BY must always come before ORDER BY in the SELECT command syntax.
2.	The HAVING clause is part of the GROUP BY clause.
3.	The ORDER BY clause returns all the fields in a specified column; the GROUP BY clause returns only the unique instances in the specified column, editing out repeated data.
4.	The grouping functions need to have a GROUP BY clause to avoid grouping on all rows.
5.	If there is a nongrouping field specified in addition to the grouping field, the GROUP BY clause is required.