具有 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 |
+----------+-----------+