具有 AGGREGATE 功能的 GROUP BY

表订单

+---------+------------+----------+-------+--------+
| `orderid` | customerid | customer | total | items  |
+---------+------------+----------+-------+--------+
|       1 |          1 | Bob      |  1300 |     10 |
|       2 |          3 | Fred     |   500 |      2 |
|       3 |          5 | Tess     |  2500 |      8 |
|       4 |          1 | Bob      |   300 |      6 |
|       5 |          2 | Carly    |   800 |      3 |
|       6 |          2 | Carly    |  1000 |     12 |
|       7 |          3 | Fred     |   100 |      1 |
|       8 |          5 | Tess     | 11500 |     50 |
|       9 |          4 | Jenny    |   200 |      2 |
|      10 |          1 | Bob      |   500 |     15 |
+---------+------------+----------+-------+--------+

• 计数

返回满足 WHERE 子句中特定条件的行数。

例如：每个客户的订单数量。

SELECT customer, COUNT(*) as orders
FROM orders
GROUP BY customer
ORDER BY customer

结果：

+----------+--------+
| `customer` | orders |
+----------+--------+
| `Bob`      |      3 |
| `Carly`    |      2 |
| `Fred`     |      2 |
| `Jenny`    |      1 |
| `Tess`     |      2 |
+----------+--------+

• 和

返回所选列的总和。

例如：每个客户的总数和项目总和。

SELECT customer, SUM(total) as sum_total, SUM(items) as sum_items
FROM orders
GROUP BY customer
ORDER BY customer

结果：

+----------+-----------+-----------+
| `customer` | sum_total | sum_items |
+----------+-----------+-----------+
| `Bob`      |      2100 |        31 |
| `Carly`    |      1800 |        15 |
| `Fred`     |       600 |         3 |
| `Jenny`    |       200 |         2 |
| `Tess`     |     14000 |        58 |
+----------+-----------+-----------+

• AVG

返回的平均数值的列的值。

例如：每个客户的平均订单价值。

SELECT customer, AVG(total) as avg_total
FROM orders
GROUP BY customer
ORDER BY customer

结果：

+----------+-----------+
| `customer` | avg_total |
+----------+-----------+
| `Bob`      |       700 |
| `Carly`    |       900 |
| `Fred`     |       300 |
| `Jenny`    |       200 |
| `Tess`     |      7000 |
+----------+-----------+

• MAX

返回某个列或表达式的最高值。

例如：每位客户的最高订单总额。

SELECT customer, MAX(total) as max_total
FROM orders
GROUP BY customer
ORDER BY customer

结果：

+----------+-----------+
| `customer` | max_total |
+----------+-----------+
| `Bob`      |      1300 |
| `Carly`    |      1000 |
| `Fred`     |       500 |
| `Jenny`    |       200 |
| `Tess`     |     11500 |
+----------+-----------+

• MIN

返回某个列或表达式的最小值。

例如：每个客户的最低订单总额。

SELECT customer, MIN(total) as min_total
FROM orders
GROUP BY customer
ORDER BY customer

结果：

+----------+-----------+
| `customer` | min_total |
+----------+-----------+
| `Bob`      |       300 |
| `Carly`    |       800 |
| `Fred`     |       100 |
| `Jenny`    |       200 |
| `Tess`     |      2500 |
+----------+-----------+