Learning Mysql

| 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 inset 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.

Download 4.24 Mb.

Share with your friends: