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:

• Aggregate - Constituting or amounting to a whole; total. ~American Heritage Dictionary

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:

<?php
// Make a MySQL Connection

$query = "SELECT type, MIN(price) FROM products GROUP BY type"; 
  
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
 echo $row['type']. " - $". $row['MIN(price)'];
 echo "<br />";
}
?>

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:

Clothing - $32.50
Food - $8.73
Music - $3.99
Toy - $3.99

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.

Posted in: MySQL