Database Reference
In-Depth Information
each host, use two grouping columns. This produces a result with nested groups (groups
within groups):
mysql> SELECT srcuser, srchost, COUNT(srcuser) FROM mail
-> GROUP BY srcuser, srchost;
+---------+---------+----------------+
| srcuser | srchost | COUNT(srcuser) |
+---------+---------+----------------+
| barb | saturn | 2 |
| barb | venus | 1 |
| gene | mars | 2 |
| gene | saturn | 2 |
| gene | venus | 2 |
| phil | mars | 3 |
| phil | venus | 2 |
| tricia | mars | 1 |
| tricia | saturn | 1 |
+---------+---------+----------------+
The preceding examples in this section used COUNT() , SUM() , and AVG() for per-group
summaries. You can use MIN() or MAX() , too. With a GROUP BY clause, they return the
smallest or largest value per group. The following query groups mail table rows by
message sender, displaying for each the size of the largest message sent and the date of
the most recent message:
mysql> SELECT srcuser, MAX(size), MAX(t) FROM mail GROUP BY srcuser;
+---------+-----------+---------------------+
| srcuser | MAX(size) | MAX(t) |
+---------+-----------+---------------------+
| barb | 98151 | 2014-05-14 14:42:21 |
| gene | 998532 | 2014-05-19 22:21:51 |
| phil | 10294 | 2014-05-19 12:49:23 |
| tricia | 2394482 | 2014-05-14 17:03:01 |
+---------+-----------+---------------------+
You can group by multiple columns and display a maximum for each combination of
values in those columns. This query finds the size of the largest message sent between
each pair of sender and recipient values listed in the mail table:
mysql> SELECT srcuser, dstuser, MAX(size) FROM mail GROUP BY srcuser, dstuser;
+---------+---------+-----------+
| srcuser | dstuser | MAX(size) |
+---------+---------+-----------+
| barb | barb | 98151 |
| barb | tricia | 58274 |
| gene | barb | 2291 |
| gene | gene | 23992 |
| gene | tricia | 998532 |
| phil | barb | 10294 |
| phil | phil | 1048 |
| phil | tricia | 5781 |
| tricia | gene | 194925 |
Search WWH ::




Custom Search