MySQL GROUP BY — Aggregate Functions
After you have mastered the basics of MySQL, it’s time to take the next step and take on Aggregate Functions. Before we talk about what they are, let’s review the definition of aggregate, as it relates to MySQL:
With this type of wording, we can assume that MySQL’s aggregate functions are something that will be very top-level, or in other words, the opposite of detailed.
The most common types of aggregate functions let you find out things like the minimum, maximum and even the average of a «grouped» set of data. The trick to understanding aggregate functions is often understanding what kind of data is being grouped and analyzed.
MySQL GROUP BY — The Data
Before we can start throwing around these fancy functions, let’s build an appropriate table that has enough data in it to be meaningful to us. Below is the SQL for our «products» table. You can either run this SQL statement in your MySQL administrator software or use MySQL to execute the queries (i.e. create table, then each of the records).
You can download the products.sql file from our website. If you are new to MySQL you will need to know how to Create a MySQL Table and Insert a MySQL Row.
Below is the MySQL table products.
Products Table:
| id | name | type | price |
|---|---|---|---|
| 123451 | Park’s Great Hits | Music | 19.99 |
| 123452 | Silly Puddy | Toy | 3.99 |
| 123453 | Playstation | Toy | 89.95 |
| 123454 | Men’s T-Shirt | Clothing | 32.50 |
| 123455 | Blouse | Clothing | 34.97 |
| 123456 | Electronica 2002 | Music | 3.99 |
| 123457 | Country Tunes | Music | 21.55 |
| 123458 | Watermelon | Food | 8.73 |
GROUP BY — Creating Your First «Group»
Imagine that our store was running an advertisement in the newspaper and we wanted to have a «bargain basement» section that listed the lowest price of each product type. In this case we would be «grouping» by the product type and finding the minimum price of each group.
Our query needs to return two columns: product type and minimum price. Additionally, we want to use the type column as our group. The SELECT statement we are about to use will look different because it includes an aggregate function, MIN, and the GROUP BY statement, but otherwise it isn’t any different than a normal SELECT statement.
PHP and MySQL Code:
Our «products» table has four types of products: Music, Toy, Clothing and Food. When we GROUP BY type then we get one result for each of these types.
Display:
MySQL GROUP BY — Review
Group BY is good for retrieving information about a group of data. If you only had one product of each type, then GROUP BY would not be all that useful.
GROUP BY only shines when you have many similar things. For example, if you have a number of products of the same type, and you want to find out some statistical information like the minimum, maximum, or other top-level info, you would use GROUP BY.
Some technical rules of GROUP BY:
- The column that you GROUP BY must also be in your SELECT statement.
- Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column.
The next few lessons will provide a walkthrough for using other popular MySQL aggregate functions in conjunction with the GROUP BY statement.
Download Tizag.com’s MySQL Book
If you would rather download the PDF of this tutorial, check out our MySQL eBook from the Tizag.com store. You may also be interested in getting the PHP eBook
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson — Your input is what keeps Tizag improving with time!
PHP Group By with Arrays
By far the most common idiom when using SQL from a web application (PHP or otherwise) is simply listing records. The standard logic looks something like this (give or take real templating):
That’s all well and good, but in practice can be quite limiting. Why? Because you can’t then group records, that is, display not one but several tables, one for each color. SQL, of course, offers a GROUP BY clause. That doesn’t do what we want, however. GROUP BY is an aggregate clause, and is used for creating totals and summaries of records. We want to cluster records by a field that is not the ordering field, or a value that is calculated off of the record itself.
I’ve generally used two different methods for PHP-side grouping, one of them much cleaner and more flexible at the cost of a little performance.
Method 0
I’m including this method as the worst way, for completeness. This can often be the first idea that comes to mind but it’s generally poor because it has a variable number of queries.
Let’s use our previous example where we have a table containing things that have a name, size, and color, and of course an ID. For readablity, we’ll also introduce a new function that renders a given record (as a table row or a list item or whatever) so that we can focus on the logic around it.
Now, here’s the lazy method, which you really shouldn’t do:
$colors = mysql_query ( «SELECT DISTINCT color from things ORDER BY color» );
while ( $color = mysql_fetch_object ( $colors )) $result = mysql_query ( «SELECT * FROM things WHERE color=’ < $color ->color > ‘ORDER BY name, size» );
print «
| Name | Size | Color |
|---|
\n» ;
>
?>
See why this method is so bad? For n groups, you’re always executing n+1 queries. That’s an unnecessary performance hit. It also means that if you’re summarizing a write-heavy table (say, a logging table) you could easily have your data change between queries from another simultaneous user. That makes any summary reports suspect. If you know you have only a small, fixed number of groups (say, there’s only 3 colors in the system, ever) then you can sometimes get away with this method, but it’s still not a good idea.
Method 1
The somewhat more performant method involves queuing records and testing edge conditions.
Now, let’s build our color-based table. What we’ll do is print the start of each list, then when we detect that we’ve changed our key value we’ll print our ending and start over.
Take a moment to read that over and see what’s going on. We’re building up each record until we see that our key value has changed. Then we flush it out, conditionally closing the previous table unless it’s the first table in which case the previous table doesn’t need to be closed because there isn’t one. That last sentence should have made you blink with that many conditionals. And then let us not forget the last iteration, which we need to flush out separately.
While a bit better than n+1 queries, since we’re running only a single query, the PHP for it is rather hairy. The actual display code is duplicated, which means changing anything means changing it twice. If the logic is at all more complicated than just a print, then that could be quite a challenge. It’s also harder to read; we have two extra control variables and two extra conditionals. Yuck.
Method 2
So what’s better? Pre-process the data. PHP makes nested arrays fast and flexible, so let’s use them.
$result = mysql_query ( «SELECT tid, name, size, color FROM things ORDER BY color, name, size» );
$set = array();
while ( $record = mysql_fetch_object ( $result )) $set [ $record -> color ][] = $record ;
>
foreach ( $set as $color => $records ) print «
| Name | Size | Color |
|---|
\n» ;
>
?>
That’s certainly much shorter. It’s also easier to follow. In part one, we cluster our records however we want. Then we simply foreach() over each one. Output code exists only once, but we stil get the same grouping effect. It’s also very extensible. Suppose we now wanted to first group records by the first letter of their name, then by color. All we have to do is add an extra level of nesting.
$result = mysql_query ( «SELECT tid, name, size, color FROM things ORDER BY color, name, size» );
$set = array();
while ( $record = mysql_fetch_object ( $result )) $set [ $record -> name [ 0 ]][ $record -> color ][] = $record ;
>
ksort ( $set );
foreach ( $set as $letter => $colors ) print » \n» ;
foreach ( $colors as $color => $record ) print «
| Name | Size | Color |
|---|
\n» ;
>
>
?>
Note that we’re also adding a ksort() line in there. Remember that we’re fetching records by color, then name. That means we could get back [blue, shirt], [green, pants] , which the while loop will then turn into an array with keys [s, p], which is backwards. The ksort() will properly order the top array while maintaining the key/value associations for the lower arrays.
You only need to add in the extra sorting step if the value you’re grouping on is a value you want to display and you want the final lists further down to order by something else. In actual practice, it’s quite rare that you really need to do that so you can simply reorder the ORDER BY clause to follow your groupings, then let your SQL engine do the work for you.
But what about the performance cost of that extra iteration? If you’re dealing with thousands of records, that could get expensive. While we can’t eliminate that hit completely, we can potentially eliminate a different loop instead.
PHP, by default, will actually buffers the results from many types of SQL databases, including MySQL. That is, when you run mysql_query() , what you get back is not a direct connection to the SQL database; PHP issues a query, gets back a reference to a result set, iterates through the entire result set and builds up a PHP resource that is a list of records, then closes the record set on the database server. That makes the mysql_query call a bit slower, but subsequent row fetches are faster since there’s no trip to the SQL database. It also means that even if you have multiple result sets available in PHP, there’s only one real result set ever open on the SQL server at a time. Many database engines won’t allow an individual connection to have more than one result set open at a time, so PHP’s buffering allows you as a developer to prentend that you can have as many record sets open as you want.
If we know that we’re not going to have to issue another query from within any of the foreach() loops, however, then we can tell PHP to not loop internally since we’re going to do it ourselves. In MySQL, that’s done using mysql_unbuffered_query():
$result = mysql_unbuffered_query ( «SELECT tid, name, size, color FROM things ORDER BY color, name, size» );
$set = array();
while ( $record = mysql_fetch_object ( $result )) $set [ $record -> color ][] = $record ;
>
mysql_free_result ( $result );
foreach ( $set as $color => $records ) print «
| Name | Size | Color |
|---|
\n» ;
>
?>
Note that we are explicitly freeing the $result object when we’re done with it. While PHP is nice about cleaning up unused memory when it goes out of scope, we need to be more polite to the MySQL server. By removing some of PHP’s intermediary magic we can get a nice performance boost at the cost of features we’re not going to be using here anyway. Score.