Advanced Querying - Learning MySQL - page 234

| track_id | track_name | artist_id | album_id | time |
+----------+----------------------+-----------+----------+----------+
| 0 | Elegia | 1 | 1 | 00:04:93 |
| 1 | In A Lonely Place | 1 | 1 | 00:06:26 |
| 2 | Procession | 1 | 1 | 00:04:28 |
...
+----------+----------------------+-----------+----------+----------+
86 rows in set (0.00 sec)
Other aggregate functions
We've seen examples of how the COUNT( ) function can be used to tell how many rows
are in a group. Here are other functions commonly used to explore the properties of
aggregated rows:
AVG( )
Returns the average (mean) of the values in the specified column for all rows in a
group. For example, you could use it to find the average cost of a house in a city,
when the houses are grouped by city:
SELECT AVG(cost) FROM house_prices GROUP BY city;
MAX( )
Returns the maximum value from rows in a group. For example, you could use it
to find the warmest day in a month, when the rows are grouped by month.
MIN( )
Returns the minimum value from rows in a group. For example, you could use it
to find the youngest student in a class, when the rows are grouped by class.
STD( ) or STDDEV( )
Returns the standard deviation of values from rows in a group. For example, you
could use it to understand the spread of test scores, when rows are grouped by
university course.
SUM( )
Returns the sum of values from rows in a group. For example, you could use it to
compute the dollar amount of sales in a given month, when rows are grouped by
month.
There are other functions available for use with GROUP BY ; they're less frequently used
than the ones we've introduced. You can find more details on them in the MySQL
manual under the heading “GROUP BY (Aggregate) Functions.”
The HAVING Clause
You're now familiar with the GROUP BY clause, which allows you to sort and cluster data.
You should now be able to use it find out about counts, averages, minimums, and
maximums. This section shows how you can use the HAVING clause to add additional
control to the aggregation of rows in a GROUP BY operation.